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.
Logon to SQL server ( source DB ) and or make A DB
Make Sure the DB is in full Recovery mode !
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
The next step is backup the DB
Backup database Moss2007
to disk = ‘c:moss2007.bak’
with format
go
And backup the Database Log files
backup log MOSS2007
to disk = ‘c:moss2007log.bak’
go
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.
On the Source server we follow the menu task<>mirror <> configure security. We can’t do anything else here yet.
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.
Next choose the target server , you will get a SQL logon window , logon and proceed
Now if you run SQL on a service account than use this account for the mirror.
And finish
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.
This shows the DB mirror console nice info can be found here.