April 3, 2013 at 9:34 am
Background
I have a number of production databases that are currently on the local disk on the server. We have recently acquired some HP EVAs which should dramatically improve performance once the DBs are moved over.
We are using SQL Server 2005 Standard Edition:
Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
Nov 24 2008 16:17:31
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Problem
The databases in question provide the backend to our websites, the websites serve users globally, so they must have as much uptime as possible.
My Current Theory
Set up transactional replication, use the SAN as the slave, once it's all set up and running, and while servers are quiet:
(1) Switch the databases to single user mode to kick everyone else off.
(2) Do an ALTER DATABASE ... MODIFY FILE ... and switch over to the slave files on the SAN.
(3) Stop and restart the SQL services
Questions
I'll be honest, I've not really used replication much, I have some logshipping jobs set up, but no replication at the moment. As you may have gathered, downtime is a big issue.
(1) Has anyone done this before, and if so, are there any problems you can foresee that I've missed?
(2) Do you think this is the best option for minimal downtime?
(3) Are there any alternatives that might work better?
Thanks in advance 😀
April 3, 2013 at 12:34 pm
I probably would not use replication for this simply because of the potential for problems with consistency at time of cutover.
How about:
1. log ship each database to a new database on the same instance with a different name, e.g. log ship DatabaseName to DatabaseName_new where DatabaseName_new's data files are on the SAN
2. when you're ready to cutover take a tail log backup from DatabaseName the primary database will take it offline as far as users can tell
3. restore the tail log backup to the secondary database and recover it, so DatabaseName_new is online at this point
4. verify that DatabaseName_new has everything in it and is ready to become the primary database
5. recover DatabaseName and immediately detach it from the instance
6. rename DatabaseName_new to DatabaseName
If you run into trouble after cutover you can always cut your losses and rename DatabaseName back to DatabaseName_new and re-attach the original database files to the instance to bring back DatabaseName in it's previous form.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 4, 2013 at 1:02 am
Thanks for the reply opc. I had briefly thought about something like you suggested but figured the replication method would be faster (less downtime) as it should only require a quick restart of the sql server rather than moving files over manually first and running code to get things back online (there are about a dozen DBs). Although I suppose I could have everything scripted for all databases, and ready to run with a single button press, after moving the tail files.
You mentioned potential problems with consistency at the cutover time with the replication method, can you explain what you mean by that please?
April 4, 2013 at 3:11 am
So I've followed the first SSC stairway step for replication to make sure i did everything correct, did it on a dev box with a test DB and some of my own sample tables.
I'm starting to see what you mean about consistency opc.three
I've successfully set up the publication and subscription, everything is running along fairly well, I set it to replicate everything in the tables; but I've noticed one of the tables hasn't replicated over for some reason, and, in the subscription database there are significantly more SPs, they must be to do with the subscription jobs to get the data or verify it or something. I'm sure these could be easily identified and removed later though. There are a lot of options when setting it up too, it's not as simple as I imagined to just replicated everything in the DB, exactly as it is in the original DB.
I need everything to be exactly the same on the SAN as it is on the local disk, so there may be too many variables here to guarantee that.
So atm it looks like I'll be using the 'full backups then tail the DBs' method as you suggested opc 🙂
April 4, 2013 at 6:08 am
FYI: Some tables will not replicate if you don't have a Primary key on them.
April 4, 2013 at 6:10 am
aaah, that explains the table that never replicated then, its the only one with no PK, thanks for the info 🙂
April 4, 2013 at 6:13 am
Thanks for posting back. I was not sure how to easily get across the concern about consistency so I am happy you went through some of the steps and got what I was saying. I have maintained for years that the Replication subsystem might have more options within it than any other subsystem in SQL Server, yet it sounds so simple to say "let's just Replicate the databases over to this other instance", as if there is a button on a screen somewhere in SSMS that just says "Replicate to..." and you just enter the destination instance name and it magically reads our minds and sets everything up. Ahh...that was a nice little daydream 😀
To keep downtime to a minimum consider the newly inserted "step 2" shown below. This will ensure the LS jobs are disabled, which I should have mentioned earlier, and will leave the DBs online while bringing the remainder of the log activity onto the secondary DBs. This will make sure the tail log backup is extremely small which should keep the time the DB is offline to a minimum. Since everything is on the same instance you could easily script 2,3 and 4, manually verify 5 or script that too, and script 6 and 7. If all goes as planned the downtime could be only a few seconds per database. Of course there will be a disruption in service but the DBs should be available again almost immediately.
1. log ship each database to a new database on the same instance with a different name, e.g. log ship DatabaseName to DatabaseName_new where DatabaseName_new's data files are on the SAN
2. when you're ready to cutover disable the LS jobs and then run the backup, copy and restore jobs one last time
3. then take a tail log backup from DatabaseName the primary database will take it offline as far as users can tell
4. restore the tail log backup to the secondary database and recover it, so DatabaseName_new is online at this point
5. verify that DatabaseName_new has everything in it and is ready to become the primary database
6. recover DatabaseName and immediately detach it from the instance
7. rename DatabaseName_new to DatabaseName
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 8, 2013 at 8:21 am
I was going to suggest setting up a database mirror. This would allow you to failover to the mirrored database with minimum downtime. If the SAN version of the DB is on the same SQL instance as the primary version then the SAN version would need a different DB name. After failover you would have to break the mirroring and rename the database to the original version.
However, having seen the suggestion to use log shipping I think that probably needs the lowest skill level to get it working. As already mentioned it will need some down-time at point of cutover, but you should be looking at less than 5 minutes.
I would only use replication for this if I wanted to make things really complicated...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 8, 2013 at 9:12 am
EdVassie (4/8/2013)
However, having seen the suggestion to use log shipping I think that probably needs the lowest skill level to get it working. As already mentioned it will need some down-time at point of cutover, but you should be looking at less than 5 minutes.I would only use replication for this if I wanted to make things really complicated...
Agreed on all counts. Mirroring could be used and I would also favor it over Replication. Ross.M mentioned they had some Log Shipping experience so it seemed like the path of least resistance to deliver a working solution. I think LS and Mirroring would both result in an almost equivalent end-user experience provided things were scripted ahead of time like I mentioned.
@Ross.M One other item worth mentioning about the LS solution: when you're happy with running on the version of the database with its files on the SAN you'll want to remember to drop the LS configuration from the instance to clear out the jobs and metadata entries from the system tables.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2013 at 2:54 am
Thanks for all the info, much appreciated! I'll go with the log shipping method as like you say, I'm the most familiar with that. I have a week (in amongst other work) to get it all sorted and ready to go, which should be more than enough time, and I have a dev box to play with and make sure everything's right, so I'll take your steps on board opc.three and hopefully everything should tick along smoothly 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply