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 Backup (impossible for some points?)

Hello,

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM. Is the transaction log
truncated at each full backup? If so, even if you take transactional
log backup every ten minutes, and full backups every once in a while,
there will be some point in time which cannot be recovered to, namely
the time between a transactional log backup and a full backup. Take a
log backup at 4:50, and full backup at 5:00 and you can never recover
to 4:55, can you?

Any insight on the topic will be appreciated,

Regards,

M. Baris Caglarmcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.

This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.

Quote:

Originally Posted by

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.

Quote:

Originally Posted by

Is the transaction log truncated at each full backup?

No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.

But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.

There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Baris

Erland Sommarskog wrote:

Quote:

Originally Posted by

mcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

I am using SQL Server 2000 with SP4. I have a database with two full
backups at 4:00 PM and 5:00 PM and a transactional log backup at 5:30
PM. Is there a possible way to do a point in time restore to 4:30 PM,
that is between two full backups?

>
Yes, restore the backup from 16:00 with NORECOVERY and then the
transaction log with the STOPAT option. Check the exact syntax in
Books Online.
>
This presumes that the log chain was never broken. That is the most
previous T-log backup of any kind must have been taken before 16:00.
SQL Server will tell you if this is the ase.
>

Quote:

Originally Posted by

When I try to use the transactional log backup that is taken at 5:30, I
can never specify a time before 5:00 PM.

>
Don't really know what you mean, but if you are using some GUI, I
don't really know what happens. I prefer to use T-SQL commands.
>

Quote:

Originally Posted by

Is the transaction log truncated at each full backup?

>
No. BACKUP DATABASE backs up the database, and all it does with the
log is to write a log record.
>
But if the database was taken as part of a job, that job may include a
backup of the transaction log as well. At worst, it includs a backup
with any of the options TRUNCATE_ONLY of NO_LOG which just throws
the logs away, without saving them anywhere.
>
There are tables in msdb where you can see at which points various sorts
of backups were taken. I don't use these tables very often myself, so
I can't give you an exact query to run.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||mcaglar@.cs.ucf.edu (mcaglar@.cs.ucf.edu) writes:

Quote:

Originally Posted by

I had find the exact same solution at a different thread in this group
and it worked, but thank you for your response. Interestingly,
Enterprise manager does not allow to perform such action. I wonder if
this was a bug or a design issue. Does anyone know if this peoblem is
fixed on SQL Server 2005?

Enterprise Manager is not included in SQL 2005, neither is Query Analyzer.
Both tools have been superceded by SQL Server Management Studio. Whether
the GUI dialogs in Mgmt Studio would make this operation available to you
I don't know. In any case, the GUI are just wrappers on the T-SQL commands,
and you can always use T-SQL when the GUI does not expose a certain piece
of functionality.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Plotting lines and data points on scatter chart

I'm trying to produce a chart that has both actual data values from a database and matching "line of best fit" plots on the one chart. I have 4 data series, 2 of the actual data values and 2 that represent the values for the "line of best fit". What I want to do is:

Plot the actual values just as data points (joining them with lines in meaningless) and

Plot the "line of best fit" values as a line.

When I edit the data series on the scatter chart, I can see the "Plot data as line" option but it is "greyed" out.

Have I missed something really simple here or is this not possible. I'm using RS2005

Many thanks

Simon

Hi Simon,

I have the same problem now.

Have you found a solution for your problem?

Thanks

Gerd

Plotting lines and data points on scatter chart

I'm trying to produce a chart that has both actual data values from a database and matching "line of best fit" plots on the one chart. I have 4 data series, 2 of the actual data values and 2 that represent the values for the "line of best fit". What I want to do is:

Plot the actual values just as data points (joining them with lines in meaningless) and Plot the "line of best fit" values as a line.

When I edit the data series on the scatter chart, I can see the "Plot data as line" option but it is "greyed" out.

Have I missed something really simple here or is this not possible. I'm using RS2005

Many thanks

Simon

Hi Simon,

I have the same problem now.

Have you found a solution for your problem?

Thanks

Gerd