INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."
Where in the world do Tek-Tips members come from?
Database Mirroring for Newbies in SQL 2005
Posted: 17 Oct 07
I had a hell of time trying to get database mirroring working in SQL 2005. We have a VPN between two sites (DR and our main office) and I wanted to replicate a DB across.
Ensure that BOTH your SQL Servers are running at least SP1 (although I have only tested it using SP2). Database mirroring is only supported using Standard and Enterprise editions.
The following steps are essentially for my configuration, in particular I haven't got a witness server setup. (A witness can be setup for any edition of SQL)
For more information see MSDN or Technet: http://msdn2.microsoft.com/en-us/library/ms177412.aspx
Off we go:
-- Take a backup of your database from the principle SQL server:
BACKUP DATABASE [MyDatabase]
TO DISK = 'C:\MyDatabase.bak'
-- Next take a backup of the log file:
BACKUP LOG [MyDatabase]
TO DISK = 'C:\MyDatabaseLog.bak'
-- Copy these files to the local filesystem of your mirror SQL server.
-- You must now restore them, note the NORECOVERY flag. RESTORE THE FILE SEPERATELY. DO NOT TRY TO RESTORE BOTH THE DB AND THE LOG AT THE SAME TIME!
-- Ensure that the full path is valid. E.G. If on the principle the MDF and LDF files were kept on T:\Microsoft SQL Server\MSSQL.1\MSSQL\Data then you must have this folder structure on your mirror server too
RESTORE DATABASE [MyDatabase]
FROM DISK = 'C:\MyDatabase.bak'
RESTORE LOG [MyDatabase]
FROM DISK = 'C:\MyDatabaseLog.bak'
WITH FILE=1, NORECOVERY
-- Until mentioned otherwise, the following SQL statements needs to be rn on BOTH your servers that you want to setup for mirroring.
-- The following two SELECT statements will show you if you have any mirroring endpoints already setup:
SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints
SELECT name, port FROM sys.tcp_endpoints
-- If you have already got endpoints, but are not using them for mirroring then please delete as per below, i'm assuming that the name of the old endpoint is mirroring but it could be anything - please use the name that is listed from the results of the above query
DROP ENDPOINT mirroring
-- Create new end point on both your SQL servers
CREATE ENDPOINT mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 7022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
-- Using the credentials that both your SQL Server instances are running under, create that user in SQL Server: (skip if already created)
CREATE LOGIN [DOMAIN\SQLServer] FROM WINDOWS;
-- With the user account that SQL uses to run it's process created in SQL Server itself as a logon, you must grant it permission to use the endpoint. You MUST make sure you do this on BOTH servers!!!:
GRANT CONNECT on ENDPOINT::Mirroring TO [DOMAIN\SQLServer];
-- VERY IMPORTANT BIT - READ THE NEXT LINE CAREFULLY!
-- On the server that you want to be the mirror (not the principle), run the following. Do NOT run this on the principle yet
-- Ensure that you change [databasename] to the DB that you want to mirror, and change the server.domain.com to the FQDN of the principle server. You may also need to change the port if you didn't use port 7022
ALTER DATABASE [databasename] SET PARTNER = 'TCP://principle.domain.com:7022'
-- Once the above command is completed, run the same on your principle server. Obviously change the server FQDN to the mirror server
ALTER DATABASE [databasename] SET PARTNER = 'TCP://mirror.domain.com:7022'
-- All done - go have a beer
Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close