August 6, 2015 at 4:21 am
I have a live server ("server 1") that management wants clustered by the end of the month. There is a second dormant server that was bought and set up at the same time, I believe hardware and software installed are identical.
Software versions are Windows 2008 and SQL Server 2008 r2 enterprise.
The following plan seemed like it would work when I tested it using one tiny database in VMs, but I gather from Googling that step 4.7 is considered not a good idea.
1. Uninstall (unused) SQL Server from server 2.
2. Create four iSCSI shared volumes (for quorum, MSDTC, databases and logs.) Volumes will be connected to both servers, online on server 1, offline on server 2.
3. Migrate all data from local to shared volumes on server one. Volume drive letters will be changed so that database disk paths will be the same after as before. Files will simply be copied from local to newly attached SAN disks. (It's not just database files that will be copied, stuff used by jobs like saved DTS packages are also currently stored on the same disk as the databases.)
4.1 Backup Server 1 (image and database backups)
4.2 Uninstall SQL Server on server 1
4.3 Rename server 1 so its network name is freed up for use by cluster
4.4. Create Windows cluster
4.5 Install SQL Server clustered instance on server 1, using recycled network name, so all SQL clients will hopefully be oblivious to changes.
4.6 Install SQL Server on server 2 with add to node option
4.7 Restore backed up master and msdb databases from demolished standalone server 1 default instance over the top of the ones created by the cluster install, which has all databases located in the same place as the original standalone server.
4.8 Attach all the user databases.
So my question is, can I really not do the master and msdb restore? The likelihood of nothing going wrong if I try to copy everything inside the databases seems low.
Does the master database created by a cluster install definitely differ from that created by a stand-alone install, so that the latter shouldn't be restored over the former?
August 6, 2015 at 4:27 am
I generally recommend that system DBs (master and MSDB) never be restored across instances or servers, even if it works, the chance of lingering problems is far too high. Especially across a clustered vs nonclustered instnace
Script your logins, server permissions, linked servers, config settings, jobs, maintenance plans, certificates and keys and recreate them on the new instance.
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
August 6, 2015 at 5:12 am
Where do I start, the beginning probably
You shouldn't do this
Part-time DBA (8/6/2015)
2. Create four iSCSI shared volumes (for quorum, MSDTC, databases and logs.) Volumes will be connected to both servers, online on server 1, offline on server 2.
3. Migrate all data from local to shared volumes on server one. Volume drive letters will be changed so that database disk paths will be the same after as before. Files will simply be copied from local to newly attached SAN disks. (It's not just database files that will be copied, stuff used by jobs like saved DTS packages are also currently stored on the same disk as the databases.)
Until you've done this
Part-time DBA (8/6/2015)
4.4. Create Windows cluster
It would really pay you to read my stairway series to HA on this site starting at this link
http://www.sqlservercentral.com/articles/Failover+Clustered+Instance+(FCI)/107536/[/url]
Having said that, this would be the correct\preferred sequence of tasks
attach the dbs and create the logins, etc
Do Not do this
Part-time DBA (8/6/2015)
4.7 Restore backed up master and msdb databases from demolished standalone server 1 default instance over the top of the ones created by the cluster install, which has all databases located in the same place as the original standalone server.
The system databases from the standalone system will not have the relevant clustered info, do not migrate system dbs, you have been warned!!
Part-time DBA (8/6/2015)
So my question is, can I really not do the master and msdb restore? The likelihood of nothing going wrong if I try to copy everything inside the databases seems low.Does the master database created by a cluster install definitely differ from that created by a stand-alone install, so that the latter shouldn't be restored over the former?
Yes, there is various metadata that is different. Migrate all required objects from one system to another
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 6, 2015 at 6:38 am
Thanks for the responses. You've both convinced me I'm going to have to do scripting rather than system database restores.
Perry, I think I am going to switch to your suggested sequence. (Will read the link as well.) Thanks very much.
(I may be back once I've had more time to process this!)
August 6, 2015 at 6:43 am
Part-time DBA (8/6/2015)
Perry, I think I am going to switch to your suggested sequence
Building the single node cluster on server 2 and keeping server 1 up as a temporary name is a no brainer, you keep both online for the migration 😉
Part-time DBA (8/6/2015)
Will read the link as well
I strongly suggest you read it first and understand it well
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 6, 2015 at 6:45 am
So I can rehearse/test/get some things out of the way in advance, would it be feasible to install the cluster instance on server 2 under a new name then rename it to the server one name on the implementation day?
Would it just be a question of changing the network name in Failover admin if I did?
August 6, 2015 at 9:02 am
Part-time DBA (8/6/2015)
would it be feasible to install the cluster instance on server 2 under a new name then rename it to the server one name on the implementation day?
Yes, that is exactly what I have done in the past if you still need the live instance online meanwhile 😉
Part-time DBA (8/6/2015)
Would it just be a question of changing the network name in Failover admin if I did?
Yes, exactly that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2015 at 3:11 am
I'm struggling to understand what I'll need to do so Reporting Services still works after all of this. Probably because I don't understand Reporting Services, all work on it was done by someone else.
(I'm not aiming for higher availability, I just want it to be working as far as possible in the same way as now once the rest of SQL Server has migrated to the cluster.)
Currently SSRS is part of the default instance on server 1.
Is there a generally recommended approach for SSRS when a default instance of SQL Server (with SSRS) is migrated to a cluster?
Can the SSRS URL remain the same, given the server it's on is going to be renamed?
August 20, 2015 at 6:29 am
SSRS is not cluster aware, you can use network load balancing however.
If installing a standalone component such as SSRS into a WSFC with clustered instances of SQL Server, then you should use a separate instance name for the installation this is due to the fact that clustered sql server components do not support side by side installs of standalone components.
In this case as the instance is already installed the new FCI install will need to be installed with a new instance name.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2015 at 3:37 am
Thanks. What confused me a little is that in my test environment I seem to have ended up with what appears to be a working copy of reporting services in the default instance on the equivalent of server 1. If anything, I would have expected it to only be on the server 2 equivalent. I will proceed on the assumption that the URL will have to change, and that I should have a separate instance, this probably won't be a major issue.
August 26, 2015 at 12:36 pm
My clustering knowledge is a bit out of date, since it used to be called MSCS, but I believe you can manually add non-cluster aware services to the cluster so that it will failover with the sql cluster group. I would test this in it's own group. Same thing for SSIS. Neither are recommended though but there are resources online if you search for it. 😛
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
August 29, 2015 at 5:48 am
Part-time DBA (8/26/2015)
Thanks. What confused me a little is that in my test environment I seem to have ended up with what appears to be a working copy of reporting services in the default instance on the equivalent of server 1. If anything, I would have expected it to only be on the server 2 equivalent. I will proceed on the assumption that the URL will have to change, and that I should have a separate instance, this probably won't be a major issue.
It's possible to have ssrs and clustered engine in same instance name but only on the first node where you install the instance. This is the sequence that would produce a default instance of ssrs with a default clustered sql instance (same with named too), also the error problem you will encounter.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply