Log Shipped reporting database needs different objects, permissions
-
I have been testing log shipping, and I am wondering if the secondary database can be modified at all after log shipping is in place. Log shipping works great if both databases are identical, but it turns out that we need additional objects on the second database. Here's my problem. 1) If I take the secondary database out of standby mode, make changes, put it back in standby mode--log shipping breaks. 2) The purpose of the reporting database is so that users don't touch the production database. If I add logins, views, etc. to the primary database so that changes are shipped to the secondary, then there is nothing to stop users from querying the producton database.
I cannot use replication because of a third party application that uses a truncate function (long story, unnegotiable). DTS is relatively slow to copy all tables, so I can't use that. The Copy Database function is out of the question because of the downtime. Backup, copy, & restore from server to server can be done, but the database is expected to grow large very fast and this method will be too slow. So I'm back to log shipping.
The best that I can come up with is to at least have different permisions: 1) add all of the logins to the primary 2) backup database 3) restore to secondary in standby mode 4) change permissions on primary 5) set up log shipping. This won't work for views because they won't be created until much later.
Anyone ever try the same thing or have any creative suggestions?
Thanks,
Linda
-
Linda,
LogShipping is meant for STANDBY servers not for reporting because the user connections are going to be kill every time a log is restored! You coud though have TWO log shipping servers with let's say 6h difference and swich the users from one to the other (via DNS) ..yuck!
If dropping the user connections is not a problem, all you need to do is create everything on the Primary and DENY premission to login (sp_denylogin) to those users/groups on the primary server but not on the secondary one.
HTH
* Noel -
Linda,
Had a similiar situation. Ended up doing a backup on the primary server. Restore to the secondary server. Drop all logins and users in the DB on the secondary server. Recreate all logins, users and permissions on the secondary server. Have it all scripted. Run as a scheduled job. Not pretty.
Hope this helps
Adam
-
Linda,
Second thought - what about differential or incremental backups to keep the data transfer minimal. Obviously have to be a manual process.
AT
-
I actually got the idea to use the standby database for reporting from BOL "Using Standby Servers" that says
In standby mode, the database is available for read-only operations, such as database queries that do not attempt to modify the database. This allows the database to be used for decision-support queries or DBCC checks.
Since the other methods of duplicating the database onto another server were not working out very well, log shipping seemed to be the best solution. I had the restores scheduled only twice a day so that kicking out uses would be minimal. After I had it working pretty well, the project manager decided that it was unacceptable not to be able to modify the reporting database--particulary adding views for reports.
I thought about trying snapshot replication again, but the timeframe for a large database makes it out of the question. One luxury that I do have is disk space, so I'm going to see what I can do by adding a third database.
I am still open to any suggestions 🙂
Linda
-
Linda how big is the budget
If you can go to SAN disk then SAN replication is your best bet!
If that is too expensive the you can try the Doble Server Logshipping solution I posted above
If still too expensive then you will have to work with your vendor and see if it is posible to work a solution for Transactional Replication
Just keep in mind you will never get something for nothing
HTH
* Noel -
The budget has already been spent so I have to work with what I have. I don't think the double server log shipping idea will quite fit the bill since they also want to add / modify views.
Working with the vendor is out of the question because we are their only client who sees a need to drop the truncation option, and they will not re-write their application for an isolated case. Plus, not all of their tables have PKs. I was hoping to use transactional replication... I set it up in the test environment and brought the application to its knees when it couldn't truncate. Actually, that's how I found out about the truncate option 🙂
I had thought of setting up log shipping and on the secondary database set up replication to a third database, but decided against it. In the meantime I'll probably just set up log shipping and have them query the tables.
Thanks for the input 🙂
Linda
-
In case anyone is interested, I found a workaround for reporting from a log shipped database. First, I had to find a solution to the orphaned logins on the read-only stand-by server. I restored a backup from the primary server to the secondary server as normal (leaving the db operational), fixed the logins, backed up the database on the secondary, then restored that backup in standby mode. A bit time consuming, but it worked. Then I could set up log shipping.
Second, to add views with appropriate permissions here's what I did. I created 20 generic views on the primary database using 2 different logins with different permissions. The views were all something like "select top 1 column from table" and were named vw1, vw2, etc. Then after log shipping was set up, the views could be re-named and altered on the primary and changes were sent to the secondary leaving permissions intact. Of course I had to deny permission to these logins on the primary server after the initial backup so that users couldn't connect to that database.
So far, I've found no problems with this setup. Hopefully I won't either 🙂
Linda
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply