Solved

SQL 2008 DB mirroringQuestion:

Posted on 2011-02-16
12
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
When rebooting a vCenters 6.0 and try to connect using vSphere Client we get this issue "Invalid URL: The hostname could not parsed." When we get this error we need to do some changes in the vCenter advanced settings to fix the issue.
Teach the user how to use vSphere Update Manager to update the VMware Tools and virtual machine hardware version Open vSphere Client: Review manual processes for updating VMware Tools and virtual hardware versions: Create a new baseline group in vSp…
This video shows you how easy it is to boot from ISO images for virtual machines with the ISO images stored on a local datastore on the ESXi host.

695 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