Points to consider for Report Database.

Ok..ok, I'm trying to get some comment/ insight on what things to consider during developement of a
Reporting module with a Reporting Database. For those who had face problems, been doing something like this, maybe you like to share a tip or two.

Project:

Developing ASP/ ASP.NET applications through DNSless connection with either OLE or ADO drivers to a SQL Server Report Database. Users will login in the browser to view reports of their choice. Not using any OLAP cubes. It would simply be callling SPs and query the tables for data and displaying it in cuztomize HTML report formats. And of course with good indexing.

Project scope:

Enterprise/Factory scale -so to assume Reporting DB size easily over 50-70 GB.

QUESTIONS:

1. How many users can concurrently login to view reports? A rough estimate would be? Is there any unit of measurement to determine the number?

2. What are the best practices- in DB design and and on application development for this project case?

3. What are the hardware concerns/specs ( best/minimum recommendations)? Would network traffic play a role here?

Thanks in advance for sharing your comments.It depens on what kind of database is but anyway:

1. Quantity of users depends on database and application design (respond time, etc.)
2. Best way - to create datawarehouse
- denormalize tables (less joins)
- a lot of indexes
- access only by sp
- you could create a fake cubes (I did it - it works even better than MS cubes for my case)

3. Ask for more and get something better... ;)sql

Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.
Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>

Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>

Point in time recovery with differential backup

Consider this scenerio in SQL 2000...
I have a full backup of a DB taken at 6AM on Sunday. After the full
backup - a T-Log backup is taken every hour. At 6PM a process is run where
a differential backup is taken and the T-Log backups between 6AM and 6PM are
deleted. Should I still be able to do a point in time recovery to a time
like 3PM?
When trying to invoke a point in time recovery from SQL EM - I get a message
indicating that the earliest time allowed for me to specify is 6PM (the time
of the differential backup).
I am thinking that perhaps SQL Server does not have the ability to recover
to a point in time in the differential backup... like it's all or nothing.
Is this true or am I most likely doing something wrong?
Thanks in advance.Hi,
No, you cant do a point in recovery if your trasnaction log backup files are
deleted.
Otherwise you should have done the belwo steps:-
1. Restore the full backup with NORECOVERY to a new database
2. Restore the subsequent transaction log backups till 02 PM with NORECOVERY
3. Restore the 3PM traansaction log backup with STOPAT option and WITH
RECOVERY.
THis will do a POINT-IN-TIME recovery in the new database.
--
Thanks
Hari
MCDBA
"TJTODD" <tjtodd@.anonymous.com> wrote in message
news:es9xcqAWEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Consider this scenerio in SQL 2000...
> I have a full backup of a DB taken at 6AM on Sunday. After the full
> backup - a T-Log backup is taken every hour. At 6PM a process is run
where
> a differential backup is taken and the T-Log backups between 6AM and 6PM
are
> deleted. Should I still be able to do a point in time recovery to a time
> like 3PM?
> When trying to invoke a point in time recovery from SQL EM - I get a
message
> indicating that the earliest time allowed for me to specify is 6PM (the
time
> of the differential backup).
> I am thinking that perhaps SQL Server does not have the ability to recover
> to a point in time in the differential backup... like it's all or nothing.
> Is this true or am I most likely doing something wrong?
> Thanks in advance.
>sql