INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • 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.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Microsoft SQL Server: Setup and Administration FAQ

Synchronizing Servers

Database Mirroring for Newbies in SQL 2005 by Stevehewitt
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:

CODE

-- Take a backup of your database from the principle SQL server:
BACKUP DATABASE [MyDatabase]
    TO DISK = 'C:\MyDatabase.bak'
    WITH FORMAT
GO

-- Next take a backup of the log file:
BACKUP LOG [MyDatabase]
    TO DISK = 'C:\MyDatabaseLog.bak'
GO

-- 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'
    WITH NORECOVERY
GO

RESTORE LOG [MyDatabase]
    FROM DISK = 'C:\MyDatabaseLog.bak'
    WITH FILE=1, NORECOVERY
GO

-- 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);
GO

-- Using the credentials that both your SQL Server instances are running under, create that user in SQL Server: (skip if already created)
USE master;
GO
CREATE LOGIN [DOMAIN\SQLServer] FROM WINDOWS;
GO

-- 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!!!:
USE master;
GO
GRANT CONNECT on ENDPOINT::Mirroring TO [DOMAIN\SQLServer];
GO

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

Thanks,


Steve.

Back to Microsoft SQL Server: Setup and Administration FAQ Index
Back to Microsoft SQL Server: Setup and Administration Forum

My Archive

Resources

Close Box

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close