Combining 2x SQL Server 2008 standard x64 instance to provide redundant SQL Server VM

Hi All,

At the moment I'd like to combine my departmental SQL Server into one big SQL Server 2008 instances spread across two servers (for redundancy reason).

How/what is the best way to approach this ?
is it through clustering or Log shipping or VMware (hypervisor) High Availability features ?

Note: this DB server cannot go down as it is hosting the VCenter DB and also server monitoring database too.

Any suggestion and comment would be greatly appreciated.

Thanks.
LVL 1
jjozAsked:
Who is Participating?
 
Andrew Hancock (VMware vExpert / EE MVE^2)Connect With a Mentor VMware and Virtualization ConsultantCommented:
Clustering Failover is quicker.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> At the moment I'd like to combine my departmental SQL Server into one big SQL Server 2008 instances spread across two servers (for redundancy reason).

As per your statement, I can understand this

1. You have a SQL Server instance in Departmental server
2. You have a Big SQL Server across 2 servers with redundancy features

Kindly correct me if I am correct.

If above understanding are correct, then you can implement Transactional Replication from your Departmental server to Big SQL Server so that your data is available in your Big SQL Server too. This will act as a backup server in case of any failures in Departmental server..
0
 
jjozAuthor Commented:
uhm pardon me, I think I must reword my question again :-|

At the moment the SQL Server is spread across several smaller servers 3-4 SQL Server 2000 and 2005 with the total of 70 DB inside those servers, what I'd like to do/build is to migrate them all into SQL Server 2008 in which I must make it redundant so that if one server failed for whatever reason, the other instance can just pick it up straight away.

so from several smaller SQL Servers, I want to consolidate them all into one more powerful SQL Server 2008 x64.
but so far don't know which technology that is suitable for that or if it is possible to do in SQL Serve 2008 Standard.
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Andrew Hancock (VMware vExpert / EE MVE^2)Connect With a Mentor VMware and Virtualization ConsultantCommented:
vCenter DB is important, but if vCenter is not available, it's not the end of thw world! You can always access each host directly with vCenter client to access hosts functions.

Yes, it's awkward but not mission critical. Same really for Server Monitorinng.

I can think or more mission crticial SQL databases to want to have high availability.

You may want to check with your Database Administrators how many IOPs, do their applications need for SQL, yes it's a good idea, but some SQL databases are just not designed to work well in a virtual environment, with high transaction rates.

1. With clustering, you will have a short outage, whlst the cluster fails over.
2. HA, the same, server will go down for several minutes, whilst HA brings it back on-line onm another host.
3. Not convinced by log shipping.

Something you've not mentioned is Fault Tolerence of VMs

VMware Fault Tolerance provides continuous availability for applications in the event of server failures,  by creating a live shadow instance of a virtual machine that is in virtual lockstep with the primary instance. By allowing instantaneous failover between the two instances in the event of hardware failure, VMware Fault Tolerance eliminates even the smallest chance of data loss or disruption.

Source
http://www.vmware.com/products/fault-tolerance/overview.html

BUT, if you break a virtual machine with FT (e.g. install bad patches and cause a BSOD) this will also cause the shadow instance to break.
0
 
jjozAuthor Commented:
thanks for your reply "Fault Tolerence of VMs" --> too complex and strict requirement (not that flexible).
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
>> so from several smaller SQL Servers, I want to consolidate them all into one more powerful SQL Server 2008 x64.

Ok, in that case you need 2 Powerful SQL Server 2008 ( Not VM's) with equal capacity.
The Second server can be configured for High Availability using either Replication or Mirroring.

Reason why I mentioned that it should not be VM's is because since it is a consolidated server this SQL Server should remain as a dedicated one for SQL Server alone..
0
 
jjozAuthor Commented:
yes that is true and as for the Fault Tolerance, I think I cannot go down to that path because I will need to configure 2vCPU and FT doesn't support it yet.

So shall I just setup one big SQL Server 2008 x64 Std. VM as stand alone and then configure the Hypervisor HA ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Hypervisor HA will give you an outage of several minutes. If that's okay.
0
 
jjozAuthor Commented:
oh, so the SQL Server 2008 clustering at the OS level and then the underlying VM is just normal deployment ?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)Connect With a Mentor VMware and Virtualization ConsultantCommented:
correct, this the quickest availability component, because you need high availability at the application level. which is sql clustering.
0
 
jjozAuthor Commented:
Thanks for the reply mate, here;s what I'm going to build:

ESX1:
VM1: Windows Server 2008 Standard + SQL Server 2008 Standard (RDM into the SAN) - clustering mirror

ESX2:
VM2: Windows Server 2008 Standard + SQL Server 2008 Standard (RDM into the same SAN LUN as above) - clustering mirror



0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Yes, make sure you TEST TEST TEST and TEST clustering before transfer of databases.

and then, transfer/copy databases and get your DBAs to TEST TEST TEST and TEST.

Before going Production.
0
 
jjozAuthor Commented:
Many thanks for the reply guys.
0
All Courses

From novice to tech pro — start learning today.