Solved

SQL 2008 DB mirroringQuestion:

Posted on 2011-02-16
12
285 Views
Last Modified: 2012-05-11
Hello Experts,

I am setting up DB mirroring, with Principal Server (with W2K8 R2 OS and SQK 2008 64 bit) being a physical server - Blade G7 (high spec with 2 CPU, 6 cores each and 96 GB RAM), and a Mirror server to be set-up in VM (ESX 4.0), a witness in physical box (DL 320).

Following are the DB;s in existing three servers that need to be migrated to this new DB mirroring.
First server:  1 instance with  2 x DBs (High usage) SQL 2008 on W2K3 64 bit OS
Second server: 1 x Sharepoint farm instance with 30 DB's runnning SQl 2005 Ent on W2K3 OS.
Third server: 5 instances with a total of 15 DB's, running SQL 2005 Ent on W2K3 OS

My question is can I have multiple instances set-up in DB mirroring in order to migrate above Instances/DB's to my new DB mirroring? if so what are the limitations and pre-caution(s) I need to undertake?

Please advise.

Thanks and Regards

0
Comment
Question by:goprasad
  • 6
  • 5
12 Comments
 
LVL 12

Expert Comment

by:Máté Farkas
ID: 34917123
You can migrate all of your instances and databases into one mirror server but you have to install at least one Standard and one Enterprise instance because the partners of the mirroring session must be the same edition.
For example Standard can be mirrored only with another Standard server, but the version can be different (2008 can be mirrored with 2005 if the database compatibility level is 90).
I recomend that install on the mirror box a SQL 2008 64bit Standard and SQL 2008 64bit Enterprise and it will can be the mirror all of your databases.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 34917239
Can it be done?  Yes, but not easily, and I would ask whether db mirroring is the best fit for your business requirements.  Mirroring is great if you've got a single database per instance, but it's going to get very complicated with as many databases as you've referenced.

Let's say you have an application that connects to several of your databases, all residing on one principal instance.  If there is a database failure, and the mirrored instances takes over, will your application have problems talking to two different SQL instances?

That is only one of the considerations to be made, and it can actually be handled by using trace events, and then automating your own failover:
http://www.mssqltips.com/tip.asp?tip=1564

Take a look at the above post, along with these two, and ask yourself whether a single principal/mirror instance is really the optimal solution for as many databases are you are referencing:
http://www.code-magazine.com/Article.aspx?quickid=080083
http://www.mssqltips.com/tip.asp?tip=1701

0
 

Author Comment

by:goprasad
ID: 34920368
At this stage I have one application DB and MOS2007 SharePoint Farm DB's. MS have indicated that support to SQL 2005 is going to end in April this year. We have 2 processor SQL 64 bit enterprise license that we purchased for application DB, we just want to utilise the licensing benefit and add Sharepoint DB's, which are currently hosted in SQl 2005.
Can we have multiple instances in DB mirroring? say default instance for application Db and named instance for Sharepoint DB's?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 17

Expert Comment

by:dbaSQL
ID: 34920397
0
 

Author Comment

by:goprasad
ID: 34939776
Currently we have MOS1, MOS2 and Index server as web front end servers for Sharepoint farm. When we set-up DB mirroring, do we then need to have:
three web front-end for Principal and three web front-end for Mirror?
Please advise.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34940414
I don't believe you have to mirror them, but the redundancy of these components is definitely recommended.  

I have not mirrored a sharepoint farm, so I can't give more intuitive advice, but take a look at these links.  This one gives a couple example models for the mirrored sharepoint farm:
http://technet.microsoft.com/en-us/library/cc748824.aspx

This one has an interesting section regarding redundancy in a sharepoint farm, and it specifically says that the index server cannot be redundant.  See page 7, the 'Redundancy' and 'Database Availability':  http://www.spguru.com/Whitepapers1/MOSS%20Farm%20Architecture.pdf



0
 

Author Comment

by:goprasad
ID: 34958169
Witness server, I am planning to install sql 2008 express edition, will this be OK?
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34958206
>>SQL Express and the Workgroup Edition can be used as a witness server, but they cannot be used as a partner server in database mirroring.

http://technet.microsoft.com/en-us/library/cc917680.aspx
0
 

Author Comment

by:goprasad
ID: 34989507
Considering that physical server (principal server) is a high spec blade server and Mirror server (Virtual server) sitting in the same geographical location, and a very good bandwidth, can I have 6 x named  instances and DB's under those mirrored? Out of those 6 instances, SharePoint 2007 farm instance is the one which has most number of DB's , the rest of the 4 instances have 1 to 3 DB's.

Is this possible?




0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34989532
Your hardware is good, that is not in question.  Given the number of instances you are referring to, and the fact that you've got a sharepoint farm on one of them, I would just ask the same question I did initially --  does the db mirroring fit your your business requirements?   If so, then it should be fine, but it won't be without the overhead necessary to maintain the mirrored db's on the multiple instances.
0
 

Author Comment

by:goprasad
ID: 34993928
I have got following questions:
First: Principal and Mirrored servers with Fibre SAN storage will be sitting in the same location  I am expecting that even if we opt for High safety Synchronous DB mirroring, with bandwidth of 1 GB/sec that we currently have, I am hoping we should not be having any issues with traffic (T-logs) between Prin and Mirror server? Is this assumption correct?
Second quesion: To utilise licensing benefit, can I host 2 or more instances (with 3- 4 DB's) that are non-critical. in Principal server, without havinng mirroring them?
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 500 total points
ID: 34994031
First:  No, I don't think that is a safe assumption to make.  The simple presence of FULL safety (synchronous) impedes the workload and transaction throughput by default.  Though your servers are co-located, and you're on a 1GB line, you should not discount this.  The true impact of the chosen safety level is very dependent upon the application(s), but still, I don't believe you should discount this.  See each of the comparison tables listed within the 'Transaction Safety Levels' section:  http://msdn.microsoft.com/library/cc917681#EDAA

Second:  Yes, you can house other non-mirrored databases on the principal server.  Database Mirroring is at the database layer.  The whole instance is not mirrored.  

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Will try to explain how to use the VMware feature TAGs in the VMs and create Veeam Backup Jobs using TAGs. Since this article is too long, I will create second article for the Veeam tasks.
Teach the user how to delpoy the vCenter Server Appliance and how to configure its network settings Deploy OVF: Open VM console and configure networking:
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question