First some background:
As a SQL DBA there's a variety of options available to you when building out a read-only database for reporting. Replication, Log Shipping, and Database Mirroring are all potential options that you might consider to build out a reporting database. I won't go into potential benefits of these solutions, but will instead seek to describe a need that I faced and the solution that was implemented. This solution, I haven't seen documented anywhere in the online literature including MSDN, SqlBlog.org, etc. I was hoping to document this in order to contribute to the online community.
For several years, I have maintained a log shipped copy of my one of my production databases for reporting purposes. This reporting database had historically been rebuilt once a day around midnight and was consumed primary by reporting services to off-load that activity from the production DB server. Over time, our reporting needs have grown internationally such that there isn't a ideal "off-hours" time frame to rebuild the reporting DB without impacting report generation. What we sought to accomplish was to build a reporting database that was available 24 hours a day, had less than 1 hour latency on average from the live DB. In addition, we sought to build a solution that worked with the Standard edition of the SQL 2005 product, and to be able to keep a single consistent connection string from the client accessing the database, as we intended to also utilize this data store for some of the "read heavy" transactions coming from our internal applications.
The solution:
With the SQL 2005 product, the synonym object was what allowed us to achieve this objective. Imagine a configuration where you maintained two read only copies of your source database for reporting on a single DB server. These databases are restores of the source DB in a read only standby mode, so they are available for queries, but also able to restore additional transaction logs. We'll call these two databases the "copy". In addition, you created a third database that contained no tables, no user data or programmability objects, but at all times maintained synonyms to the all of your tables and programmability objects in the most recently refreshed copy of your source DB. We'll call this DB the "shell db". At any one time, all of your client SQL threads are connecting to the shell db and effectively using just one of the two copy databases. Since your client connections always connected to the shell db, it allows you to refresh whichever copy of your source database is not in use at that time.
Basic Diagram:
The idea is that your client sessions never connect directly to the log shipped copy of your database, instead they connect to the shell database and all of their queries and / stored procedure executions run against one of the copy databases through synonyms. When it's time to re-sync, you gracefully start dropping and recreating the synonyms to point to the next database. For users that are actively running queries, the transition should be fairly seamless.Sample code and implementation detail to follow:
