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.

Posted September 11, 2009 by Robert Smit in SQL 2008

Tagged with

  • Tag