June 10, 2014 at 1:14 pm
So here is my situation ... we have a Production SQL 2008 R2 server that is serving up a number of databases right now ... my boss just purchased a second server and wants to cluster these two servers together. From what I have read, you cannot simply upgrade the standalone server to Cluster Mode. Is this correct?
Assuming that this is correct, how would you guys recommend I go about clustering these two servers with minimal downtime to the organization??
Thanks,
Nick
June 10, 2014 at 1:25 pm
How much downtime is acceptable? How big are the databases? How similar is the hardware? What sort of storage are you working with?
As far as I know, you can't just "upgrade" an existing stand alone server to a cluster.
June 10, 2014 at 1:31 pm
Servers are identical ... Prod box has a locally attached SSD array, Second server has locally attached SAS Array ... Total DB size is 220GB.
If I can get the down time to under 4 hours that would be great!!
June 10, 2014 at 1:47 pm
You'll need shared storage for a 2008 cluster.
What types of failures are you trying to prevent with a windows cluster? Have you looked into using VMWare or Hypervisor for HA? I know not an exact replacement but it has some great advantages, and some disadvantages.
June 10, 2014 at 1:51 pm
June 10, 2014 at 3:10 pm
I will be using Steeleye Datakeeper to do the clustering of SQL 2008 ... it does not require shared storage. See below.
June 10, 2014 at 3:43 pm
ntrovato (6/10/2014)
That looks interesting. What's the advantage vs just using VMWare/Hypervisor?
They don't have any of their white papers open without registering.
I would be very wary about putting anything teir 1 on a solution without extensive documentation and wide field adoption. You're probably going to be locked in to the vendor for support if things go bad, make sure you get a support contract with a good sla.
June 10, 2014 at 7:53 pm
Quite honestly, most of these decisions were already made before I started on this project ... so I'm pretty much stuck with this solution and I need to run with it.
How would you go about making this transition?? I was thinking of doing the following:
1.) Install Server 2008 and SQL 2008 in cluster mode on the second server
2.) Backup and restore the DB's from the PROD server and restore them onto the second server
3.) The rebuild the PROD server and add it to the exisiting cluster
Does that make sense?? How would I transfer the system databases over??
June 11, 2014 at 7:38 am
ntrovato (6/10/2014)
Quite honestly, most of these decisions were already made before I started on this project ... so I'm pretty much stuck with this solution and I need to run with it.How would you go about making this transition?? I was thinking of doing the following:
1.) Install Server 2008 and SQL 2008 in cluster mode on the second server
2.) Backup and restore the DB's from the PROD server and restore them onto the second server
3.) The rebuild the PROD server and add it to the exisiting cluster
Does that make sense?? How would I transfer the system databases over??
I've built several Clusters in my life. SQL 2000, SQL 2008, and most recently, SQL 2012. I never had any problems when the hardware and storage was properly sized by the SAN or IT team. When it was not, it was a real nightmare.
Let me share with you what I've learned from experience,
#1, measure your workload
Before start moving stuff. Measure your workload. You can use perf. mon for that. Measure CPU, RAM and I/O utilization. Also, if you can attach the new storage to a Windows server, measure I/O as well using SQLIO. This is critical! Because if your storage is not fast enough, you will have performance issues. I faced lot of problems with a SQL2012 Cluster because the storage was not properly sized. The Cluster started failing over unexpectedly and I could not even take SQL backups, that will trigger a failover too. It was a nightmare. The SQL Cluster was properly configured, but the SAN was not.
#2 use same hardware
MS clearly states that you should use identical hardware. Mixing hardware, like servers with different RAM or different CPU models will create issues. Long time ago I face a problem with a SQL 2008 Cluster that IT people built with different CPUs. It experienced random failovers and issues. It took a long time to discover and much more to migrate and fix. So, you are warned 🙂
#3
Use shared storage.
MS also is clear that using share storage is recommended. You are dealing with databases, so writing stuff to disk without corruption is key. Because the way SANs work, they ensure data corruption won't occur. Some cheap storage solutions like file storage and others, can be used, but they write information in a different way and were not designed for SQL clusters, so you may end with a slow Cluster or one that may corrupt your data. So use an MS endorsed array/SAN for your Cluster.
How to migrate? Well... you can build a one node Cluster and backup and restore your databases or even use Mirroring. Then you destroy the old hardware and join that server to the Windows Cluster. This is assuming you will re-purpose existing hardware. Otherwise, you can do the same and build the Cluster 1st and do a side by side migration.
Forget about master, model, etc. You can't move those. But you can script any T-SQL job you may have. Same goes for SQL logins.
Please Google or look here for step by step process of how to build one. It is complex so I can't explain it on a few lines in a simple post. But you need to assign IPs, setup a Domain account for the SQL server service, and much more. So be prepare to work with your network or IT team, you will need them for the initial setup and on going support.
June 11, 2014 at 10:21 am
Thanks for the advice ... your explanation in step 3 was exactly what I was thinking of doing. When you say ignore the Master, System database ... is this a recommended way to do that?? Should I be scripting that stuff over?
June 11, 2014 at 11:42 am
ntrovato (6/11/2014)
Thanks for the advice ... your explanation in step 3 was exactly what I was thinking of doing. When you say ignore the Master, System database ... is this a recommended way to do that?? Should I be scripting that stuff over?
Well, think about it...
MASTER only keeps track or database location, metadata, accounts, system configutation, LinkedServer info, etc. All those are going to be recreated on the new server so there is nothing to move; those are unique for your existing instance. In fact, I don't see any other reason to backup and restore master unless is due corruption on your existing instance. On that particular case, then you need to overwrite your own master from a backup. But that requires downtime and start SQL in single user mode, and it's not needed on a side by side migration.
MODEL? is different. If you have any user created there or database specific setting, you need to script that out as well and apply on the new MODEL database, so you can keep those settings there too.
Anyway... for a side by side migration, you need to worry about logins, LinkedServers if you have one, T-SQL jobs that are running. Script those out and recreate on the single node Cluster.
June 13, 2014 at 5:39 am
ntrovato (6/10/2014)
It's replicated storage which although not strictly shared it does emulate shared storage for cross site purposes.
Do you need to keep the same SQL Server instance name?
If not, you can deploy a single node cluster using the new server, install a new clustered instance of sql server. Migrate all objects (users, agent jobs, linked srevers, etc, etc) from the standalone server to the new cluster. When you're ready down the standalone server and get the databases moved to the new cluster.
Take an image of the old server and flatten the OS. Deploy a clean OS install and join to the new cluster. Once joined, add the node to the clustered sql instance(s).
-----------------------------------------------------------------------------------------------------------
"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