SQL 2008 Database Mirroring

 

The easy things are often the hard things to do. Clustering, DCHP,DFS,SQL,SCOM,SCCM,SCDMP,MOSS,

I had this project that runs completely on SQL with a IIS .NET app and I migrated them to SQL 2008 Clustered and on Windows 2008 R2 but I had this Question I want to mirror the DB to an off site location, ok no problem just follow the wizard and do next next Finish like I always do 😉   “I hate the Wizards “ but yes they did and it won’t work. So they hired me to do the job. I saw the mistakes and see why people don’t READ. Some IT crowd just have a lot off stress to help the company to get things running again. Ok the youtube links are a bit off topic but the It crowd of this project did know all of them and we had a lot of fun during the installation in this 100% success fully project. I had again a happy customer.

But If you want to mirror the SQL DB you must not forget to do just one step use  named pipes.

So I have my 2 SQL 2008 servers running and as you can see I have the Mirror consol open and there are DB in it.

in less than 10 steps we do DB Mirroring.

clip_image002

clip_image004 clip_image006clip_image008

Logon to SQL server ( source DB ) and or make A DB

clip_image010 clip_image012clip_image014

Make Sure the DB is in full Recovery mode !

clip_image016 This is the key thing USE named pipes ! on source and target.

So now we have a database that we want to mirror, I check a few steps with a SQL script.

Just to make sure the DB is in full recovery.

use master

go

alter database Moss2007

set recovery full;

go

clip_image018 The next step is backup the DB

Backup database Moss2007

to disk = ‘c:moss2007.bak’

with format

go

clip_image020 And backup the Database Log files

backup log MOSS2007

to disk = ‘c:moss2007log.bak’

go

clip_image022 Yes I can be done easyer but this is a step by step and you can do things twice.

 

So the first server done. Lets go to the second server and copy files to other server. The backup files are now on the target server.

Maybe the path location is not the same than on the source, better is to use the same location but you can easy change this.

What I did I did an all in one job restore and change the path.  the SQL script speaks for it.

 

use master

go

restore database moss2007 from disk =’C:moss2007.Bak’ with norecovery,

replace,

move ‘moss2007’ to ‘C:sqlmoss2007.mdf’,

move ‘moss2007_log’ to ‘C:sqlmoss2007_log.ldf’

go

restore log moss2007 from disk =’C:moss2007log.bak’ with norecovery, replace,

move ‘moss2007’ to ‘C:sqlmoss2007.mdf’,

move ‘moss2007_log’ to ‘C:sqlmoss2007_log.ldf’

go

So now we moved the DB files to the C:SQL location , there is no better place than the c drive to place your DB files , if it uses all your disk space and there will be a sudden reboot the system is broken if you are lucky.

USE a disk other than the C drive.!!

 

We go back to the Source server.

clip_image024 clip_image026 clip_image028 

On the Source server we follow the menu task<>mirror <> configure security. We can’t do anything else here yet.

clip_image030 clip_image032clip_image034

So easy next <>next , in my case I have only 2 servers but better would be 3 servers the third server as a witness server,

I choose NO here, next would be source SQL server.

To support automatic failover, a database mirroring session must be configured in high-safety mode and also possess a third server instance, known as the witness. The witness is an optional instance of SQL Server that enables the mirror server in a high-safety mode session to recognize whether to initiate an automatic failover. Unlike the two partners, the witness does not serve the database. Supporting automatic failover is the only role of the witness

 

 clip_image036  clip_image038 clip_image040

Next choose the target server , you will get a SQL logon window , logon and proceed

 

clip_image042 clip_image044 clip_image046

Now if you run SQL on a service account than use this account for the mirror.

And finish

 

clip_image048 clip_image050 clip_image052

The database mirror is complete you can start it directly or in the first menu where you started. If you do a failover you will see that the DB is moved to the other location, and you app can still be up the sql server is mostly one item. remember the SQL server name is different than the first one so the app must see that the DB is moved to the other server witch can be down.

clip_image054 This shows the DB mirror console nice info can be found here.

Author: Robert Smit [MVP]

Robert Smit is Senior Technical Evangelist and is a current Microsoft MVP in Clustering as of 2009. Robert has over 20 years experience in IT with experience in the educational, health-care and finance industries. Robert’s past IT experience in the trenches of IT gives him the knowledge and insight that allows him to communicate effectively with IT professionals who are trying to address real concerns around business continuity, disaster recovery and regulatory compliance issues. Robert holds the following certifications: MCT - Microsoft Certified Trainer, MCTS - Windows Server Virtualization, MCSE, MCSA and MCPS. He is an active participant in the Microsoft newsgroup community and is currently focused on Hyper-V, Failover Clustering, SQL Server, Azure and all things related to Cloud Computing and Infrastructure Optimalization. Follow Robert on Twitter @ClusterMVP Or follow his blog https://robertsmit.wordpress.com Linkedin Profile Http://nl.linkedin.com/in/robertsmit Robert is also capable of transferring his knowledge to others which is a rare feature in the field of IT. He makes a point of not only solving issues but also of giving on the job training of his colleagues. A customer says " Robert has been a big influence on our technical staff and I have to come to know him as a brilliant specialist concerning Microsoft Products. He was Capable with his in-depth knowledge of Microsoft products to troubleshoot problems and develop our infrastructure to a higher level. I would certainly hire him again in the future. " Details of the Recommendation: "I have been coordinating with Robert implementing a very complex system. Although he was primarily a Microsoft infrastructure specialist; he was able to understand and debug .Net based complext Windows applications and websites. His input to improve performance of applications proved very helpful for the success of our project

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: