September 10, 2010 at 6:45 am
In our environment, we have a production SQL server and a reporting SQL server. Every morning the report server restores a copy of the database backup created on the production server. As part of this restore, we update a license table in the database as the application utilizes a machine specific license.
Rather than restoring a single backup file every morning, I would like to use log shipping to keep the reporting database moderately up to date throughout the day. However, log shipping leaves the reporting database in a read-only state which means I cannot update the machine specific license key stored in the database. I entertained the idea of using replication, but I do not want to be responsible for knowing when the software vendor adds new tables, stored procedures, views, etc.
Does anyone know if there is a way to update the license file with log shipping or if there is an alternative to log shipping and replication?
September 10, 2010 at 8:56 am
Create a new database on each server just for the license key table, and then create an alias in your main database for the license table to point to the new database.
Leave this license database live on both servers, update each to the correct key, and then using a log shipped read-only copy will read from the license database on whichever server the db is on.
That's the only way I can think of that wouldn't require updating publications/etc on your part.
September 10, 2010 at 10:20 am
Creating an alias sounds like a brilliant idea. However, I am not familiar with creating a table based alias. Can you include some instructions to point me in the right direction?
September 10, 2010 at 10:30 am
Sorry for the wording there..was in the middle of a few things. I meant a Synonym, not an alias.
CREATE SYNONYM [dbo].[LicenseTable] FOR [LicenseDB].[dbo].[LicenseTable]
I don't know what your table names are so you'd have to match it up..and either rename your current table, or pick a new name for the synonym and change your code to point to it.
September 10, 2010 at 10:44 am
I just tested creating a SYNONYM and it worked exactly as I needed. Thank you very much for your help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply