June 26, 2014 at 6:31 am
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target. We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do. Permissions are different on each instance, so would prefer not to overlay users on destination database. Options we are looking at right now are...
Log Shipping
Snapshot Replication
Transfer SQL Objects Task (SSIS)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.
Please let us know what you have done or what you think would work best.
Thanks.
John
June 26, 2014 at 6:49 am
Backup, copy, restore can work. You can restore as a different name then swap the old and new databases very quickly.
Log shipping will result in a read-only database at the destination. Snapshot replication will result in the entire DB disappearing as the snapshot applies, more downtime from that than restore. Transfer SQL objects is worse.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2014 at 7:02 am
UconnDBA (6/26/2014)
Looking for what others have done to keep a copy of a database, for read only, on another instance. Need to do this once a day early in the morning with no, or minimal, downtime at the source and target.
Well, based on the above i would say backup\restore or Logshipping but is dependant on the database size.
UconnDBA (6/26/2014)
We have applications that access this copy 24/7, so prefer not to disconnect active users, as a detach/attach or backup/restore might do.
So does LogShipping 😉
UconnDBA (6/26/2014)
Permissions are different on each instance, so would prefer not to overlay users on destination database.
Not sure what you mean here by overlay users, could you expand please.
If you mean grant users access, then with LogShipping that is done at the source database and fed through to the target via the log restores. The logins from the source must be recreated on the target with the same SID to avoid orphaned users.
UconnDBA (6/26/2014)
Our environment for this is SQL 2012 on Windows 2012, in the same AD domain located in the same server room. The database size is 1gb. Needs to be copied around 6:30am daily. Does not need to be updated thru the day.Please let us know what you have done or what you think would work best.
Thanks.
John
Ah, based on this it's a no brainer, a simple backup\restore would be my preferred route.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 26, 2014 at 7:33 am
Thanks for the replies! As far as overlay users I mean that the source and target will have different users/permissions. If we do a backup/restore the users at the target will now be the users that were at the source. If so I will need run a script to drop these users and recreate the target users, which is ok, but was hoping to avoid that.
My big concern with backup/restore (even if I do to another name and rename) is that if someone is actively connected to that database, they will lose that connection, and the application(s) needs to be restarted. One application is 24/7.
Currently we are doing this in our SQL 2005 environment using SSIS SQL Server Transfer Objects task. This is doing Data Only and replacing all other objects such as views, functions, stored procedures. We chose this because we thought it was the least disruptive method (does not disconnect users). If it errors during Data Only, meaning it cant load a table because of a schema change, it switches to Schema and Data also reapplying user permissions. This for the most part works, but is not perfect. Such as if an object is dropped at the source, it doesn't get dropped at the target. Or some schema changes will not get to the target because it did not make the Data Only error. In other words, it can get out of sync.
June 26, 2014 at 7:37 am
UconnDBA (6/26/2014)
As far as overlay users I mean that the source and target will have different users/permissions. If we do a backup/restore the users at the target will now be the users that were at the source. If so I will need run a script to drop these users and recreate the target users, which is ok, but was hoping to avoid that.
Whats wrong with a simple script that drops unwanted users?
UconnDBA (6/26/2014)
My big concern with backup/restore (even if I do to another name and rename) is that if someone is actively connected to that database, they will lose that connection, and the application(s) needs to be restarted. One application is 24/7.
If you restore the backup to a new server even with the same databasename, how can someone connect to it by accident, i'm assuming that's what the above means?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 26, 2014 at 7:47 am
Not concerned about anyone connecting by accident. I don't have any problem dropping unwanted users and re-creating users/permissions. Just would prefer not to disconnect anyone who is actively using and have to restart application services, if I can find a way not to. If we have any disruption to connections, such as when applying windows updates to server, the application needs to be restarted. This needs to be coordinated with the users of the application.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply