Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2008 DB mirroringQuestion:

Posted on 2011-02-16
12
Medium Priority
?
305 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 13

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 2000 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
Technology Partners: 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!

 
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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
Teach the user how to use create log bundles for vCenter Server or ESXi hosts Open vSphere Web Client: Generate vCenter Server and ESXi host log bundle:  Open vCenter Server Appliance Web Management interface and generate log bundle: Open vCenter Se…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

604 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