Options for providing a separate reporting server.....

[tas1]

Hi,
I would appreciate any tips/advice on options for providing a separate copy of a SQL 2005 database, on a separate server, for reporting against. This is for performance reasons.

The following information describes my situation;

- I have a database of about 80GB
- The reporting 'copy' only needs updating once a night
- The reporting 'copy' will be hosted close to the live database
- Ideally I need the update to be quick
- I'm using Windows 2003 virtual servers (hosted with VMware)

I know some reading up etc is required, but any tips/pointers will be greatfully received.

Thanks

 

[ptheriault]

The easiest method to maintane would be to set up a SQL db mirror to the report server. Then create a snap shot of the mirror. You can schedule a refresh of the snap shot for when ever you need. I have two snap shots set up for reporting.
1 that is refreshed hourly and the other twice a day. You can also use this server as a hot backup to your production server. It will add just another layer of protection.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[tas1]

Thanks Paul,

Does anyone have a link to a document / support note which describes how best to setup and configure a mirror and subsequent snapshot?

 

[ptheriault]

Here is how to configure/create the mirror.

Once that is done here is the syntax to create a snapshot.

USE master
GO

-- Drop snapshot database if it already exists
IF  EXISTS (
	SELECT name
		FROM sys.databases 
		WHERE name = N'[b]<ss_name>[b/]'
)
DROP DATABASE [[b]<ss_name>[/b]]
GO

-- Create the snapshot database
CREATE DATABASE [[b]<name>[/b]] ON
( NAME = [[b]<name>[/b]_Data], FILENAME = 
'F:\Program Files\MSSQL.1\MSSQL\Data\[b]<name>[/b].ss' )
AS SNAPSHOT OF [[b]Orig_DB_NAME[/b]];
GO

Snapshots are read only. If you make a change to a stored proc that you need in the snap shot database you will have to add the proc to the primary database then recreate the snapshot. Also, snapshots security is from the primary database. So if you want a user to have exec permissions on a stored proc you must grant it in the primary db. However, if you don't want that user to have access to the primary db you can disable that account on the primary.
Also, make sure that the Logins on the primary and mirror server have the same SID or you'll have problems. There is an FAQ on how to do that.

Let me know if you have any questions.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Sorry about that.
I forgot to post the link.

http://msdn2.microsoft.com/en-us/library/ms188712.aspx

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[tas1]

I've just noticed that Enterprise Edition is required to take database snapshots. As I only have Standard Edition I guess I need to find an alternative solution to the one Paul suggests.

I'm thinking it's goint to have to be transactional replication where it only 'syncronises' once a night.

Not sure what performance will be like on an 80GB database, or how big the transaction log file will be?

Any advice / tips / warnings will be greatfully received!

Thanks

 

[ptheriault]

If your going to use transactional replication I would run it more than once a day. The more frequent your transactions are applied the smaller amount of data you will send across your network.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

The one problem I found with using transaction replication is that the process has to add a field to the tables to recognize changes. The database that I utilize will not allow this as it would disturb the application logic. Any way around this? I am using sql 2000 and have no plans to upgrade to 2005.

 

[ptheriault]

It just keeps getting better. Giving your requirements you may have no choice but to install SQL Reporting services and an OLAP database.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[tas1]

Thanks for your input Paul. I'm now thinking that I'll use EVA snapshots of the filesystem and mount them on the reporting server to report against. I think this will be quicker and easier than using the SQL tools available.

 

[ptheriault]

Giving your requirements that might be your best path.

- Paul

- If at first you don't succeed, find out if the loser gets anything.