SQL Server Performance Forum – Threads Archive

Stand By Server for reporting – question

Hi, We need to set-up reporting server because our production server can’t handle OLTP and reporting in the same time <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Maybe this sounds strange but our reporting dept. want’s to have minimum delay between production and reporting servers. Our database is 120GB and we have over 100 tables in one database. I think that Log Shipping will be the best for this case but I have one question. If I set-up Log shipping (stand by mode) to synchronize every 15 minutes – what is gone happened when users will be reading data from reporting server and Log Shipping synchonization occurs. They will be dropped from database or Log Shipping fails?

The users must be removed or refrained using user database on secondary server during the instance of log restore, otherwise set a time intervals for that reporting program. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Is this option included in Log Shipping wizard?
If not, where I can put code to remove users from database before Log Shipping restore?
By the way maybe you know how to do this? Maybe replication is better solution than Log Shipping in my case?
One think… I’m little worry about how replication works with 100 tables (articles).

As Satya mentioned you can’t restore the database while users are using the database…
You need to add kill all users before log backup restores…but it is not feasible solution… Transactional Replication another option but every table should have PK if not you need to add one…
And replication complete 120GB is painful, you need to create multiple publications….don’t create single publication with 100 tables… If you guys upgrade to SQL SERVER 2005 you have another option called DATABASE MIRRORING which is right fit in this case…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks, I will try with replication first.

Make a note that replication can be processed on the table level and not on the database level, if the reporting features needs tables to provide then ensure they are involved in that process. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by MohammedU If you guys upgrade to SQL SERVER 2005 you have another option called DATABASE MIRRORING which is right fit in this case… <hr height="1" noshade id="quote"></blockquote id="quote"> Actually, mirroring does the similar to log shipping in that the database is unavailable to users while mirroring is taking place. So mirroring is only half the solution. <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> The full solution is to use Enterprise 2005 on the mirror’d database server and snapshot the mirror for reporting.

Thanks Haywood, I totally forgot db will not be available when it is syncronizing… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

No worries. The solution I mentioned works well, I’ve built a POC using a 40GB database and had pretty good success with it. The only real bugger is that the clients get disconnected when you drop a snapshot. So your reporting application has to be able to deal with the occasional serverside ‘forced’ disconnect, of which there are a couple options… 1: Use only one active snapshot with a constant name. The client can just reconnect to what it thinks is the same database all the time. 2: Create a log history table if you want multiple active snapshots (rolling histories for ex) have the client use the log table to determine which snapshot it should connect to. Works a peach, just make sure that your mirror’d database is on fast disk array(s) as the snapshots are typically small (sparse) and the majority of your reporting IO is on the actual mirror’d database still.

Yes we use a similar setup (with rolling Snapshots) and it works well. I think the single active snapshot sounds like a good idea though. Imho the biggest bugger is the price of Enterprise Edition!

Did any one tried to create the Snapshot when the database in standby mode when you do custom log shipping…
I will try on Monday..
If it allows then you can avoid EE cost… You can use mirroring in SE but you have to run in synchronous mode which will casue performance issue due to two commits…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Snapshot is only available in the EE version. You’ll need EE to use snapshots, so you are stuck with paying for the license no matter what.

You can NOT backup the mirror database You can NOT connect to the mirror database You CAN create a Database Snapshot against the mirroring database (only available in the Enterprise Edition since that is the only edition that supports Database Snapshots) Since you can’t backup the mirror database, you can’t restore a backup from the mirror database since you can’t create the backup that you’d need in the first place. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>