Welcome to SqlServer-QA.net Sign in | Join | Help

SQL Server Storage Engine & Tools (SSQA.net)

SQL Server Tools includes storage engine that includes a complete set of graphical tools and command prompt utilities that allow users, programmers, and administrators. SSMS, SSRS, SSIS & SSAS are most commonly used tools.
When to use mirrored server as a reporting server?

IN the case of  production server which is running 24/7 and can't afford any downtime, it is good to keep the mirrored server for reporting purpose. Say if the requriement is to have reporting server that should be updated with data from production server at least by every 30 minutes. Bear in mind both Log shipping and Replication can't be used here as  in this method when you delete records (old records) from production server this will also remove the rows from reporting server.

So to achieve this you could take help of database mirroring in this case and keep the mirroring server for reporting purpose to reduce the stress on live server in this case, further you can perform timely based mirroring for reporting purpose if you don't want to update reporting server database immediately. The configuration within DB Mirroring is: For reporting only run with safety off and no witness. Either one is enough to disable auto failover, but quorum (2 of 3) is required to run with safety on so with no witness both systems must be up to update the principal server. Safety off allows running with the mirror down and no witness. To use the mirror as a reporting server you can create database snapshots on an active mirror. This allows point in time read only access to the database.  Further you can create a database snapshot on top of it and use it for as long as you need, let say for reporting purposes. You are able to logship from the principal (live) database if you desire multiple reporting servers. Bear in mind for taking advantage of SNAPSHOT backups you must have Enterprise Edition.

Posted: Wednesday, August 08, 2007 1:09 AM by SQL Master

Comments

Other SQL Server Blogs around the Web said:

IN the case of production server which is running 24/7 and can't afford any downtime, it is good

# August 8, 2007 3:24 AM

SSQA.net - SqlServer-QA.net said:

IN the case of production server which is running 24/7 and can't afford any downtime, it is good

# August 8, 2007 3:53 AM
Anonymous comments are disabled