December 9, 2008 at 1:30 pm
Hi All,
I'm attempting to get database mirroring setup on a database but am running into difficulties. I'm following the Database Mirroring in SQL Server 2005 guide found at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx. I'm specifically running into issues on the Setup and Security subsection of Database Mirroring Dynamics.
I've got two servers, not on the same domain. Therefore (as I understand it) I must use certificates. Ok. So now I've moved on to trying to get certificates created. The first thing I did was try to create a DB master key on the database I'm going to be mirroring so I can get the certificates created. I get the following error: 'An error occurred during decryption.'
I've read a few blog posts that recommend trying to regenerate the SMK ( I know the service account has already been changed). Is this the right thing to do in this situation? There is no backup of the SMK with which to do a restore.
I'm not sure if I'm even headed down the right path anymore, my head is spinning. If anyone has run into this in the past, or knows of a good mirroring tutorial, please let me know.
I know this is an extremely broad post, but any help whatsoever is greatly appreciated.
Thanks,
Nate
December 9, 2008 at 1:51 pm
Below is a set of steps I use to setup mirroring with a cert.
I have not seen your specific issue, but I don't setup mirroring everyday.
Keep at it, it is great to have. i do suggest that you setup some test servers to get a feel for it.
#### SQL Mirror Notes / Example ####
NOTE: you may need to enable DB mirroring (should not after SP1)
If you see a message such as the following:
Msg 1498, Level 16, State 2, Line 5
Database mirroring is disabled by default...
You'll need to perform the following and restart the DB:
SQL Server Config Mgr==>right-click SQL Server==>Properties==>Advanced Tab==>
Startup Params==> append the following: ;-T1400
1)===removed current config: ==================================================
NOTE: this step would (obviously) not normally be necessary
--MASTER:
drop endpoint Mirroring
drop certificate SQLSVR_ONE_cert
drop certificate SQLSVR_TWO_cert
drop certificate SQLSVR_MONITOR_cert
go
--MIRRORSVR:
N/A
--MONITORSVR:
drop endpoint Mirroring
drop certificate SQLSVR_ONE_cert
drop certificate SQLSVR_TWO_cert
drop certificate SQLSVR_MONITOR_cert
go
2)===created certs: ===========================================================
--MASTER:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ';
CREATE CERTIFICATE SQLSVR_ONE_cert
WITH SUBJECT = 'Mirroring endpoint cert',
EXPIRY_DATE = '10/31/2020';
GO
BACKUP CERTIFICATE SQLSVR_ONE_cert TO FILE = 'c:\certs\sqlsvrone.cer';
--MIRRORSVR:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ';
CREATE CERTIFICATE SQLSVR_TWO_cert
WITH SUBJECT = 'Mirroring endpoint cert',
EXPIRY_DATE = '10/31/2020';
GO
BACKUP CERTIFICATE SQLSVR_TWO_cert TO FILE = 'c:\certs\sqlsvrtwo.cer';
--MONITORSVR:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ';
CREATE CERTIFICATE MONITORSVR_cert
WITH SUBJECT = 'Mirroring endpoint cert',
EXPIRY_DATE = '10/31/2020';
GO
BACKUP CERTIFICATE MONITORSVR_cert TO FILE = 'c:\certs\MONITORSVR.cer';
See the following link for MS instructions:
http://msdn2.microsoft.com/en-us/library/ms191140.aspx
3)===exported/imported the various certs as defined above: ====================
NOTE: copied all certs to c:\certs on all three machines
--MASTER:
CREATE CERTIFICATE SQLSVR_TWO_cert FROM FILE = 'c:\certs\sqlsvrtwo.cer'
GO
CREATE CERTIFICATE MONITORSVR_cert FROM FILE = 'c:\certs\MONITORSVR.cer'
GO
select * from sys.certificates;
--MIRRORSVR:
CREATE CERTIFICATE SQLSVR_ONE_cert FROM FILE = 'c:\certs\sqlsvrone.cer'
GO
CREATE CERTIFICATE MONITORSVR_cert FROM FILE = 'c:\certs\MONITORSVR.cer'
GO
select * from sys.certificates;
--MONITORSVR:
CREATE CERTIFICATE SQLSVR_ONE_cert FROM FILE = 'c:\certs\sqlsvrone.cer'
GO
CREATE CERTIFICATE SQLSVR_TWO_cert FROM FILE = 'c:\certs\sqlsvrtwo.cer'
GO
select * from sys.certificates;
4)===created mirroring endpoints: =============================================
--MASTER:
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER,
AUTHENTICATION = CERTIFICATE SQLSVR_ONE_cert, ENCRYPTION = required)
GO
--Note: you can specify AES encryption for better security
--Partners under same domain user (per DB instance)
--Third account is used for witness: mirrormon
USE master ;
GO
--CREATE LOGIN [DOMAIN\mirrormon] FROM WINDOWS ;
--GO
-- Grant connect permissions on endpoint to login account of witness & MIRRORSVR.
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\mirrormon]
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_MONITOR_login]
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_TWO_login]
GO
SELECT * FROM sys.database_mirroring_endpoints;
--MIRRORSVR:
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=ALL,
AUTHENTICATION = CERTIFICATE SQLSVR_TWO_cert, ENCRYPTION = required)
GO
--Partners under same domain user (per DB instance)
--Third account is used for witness: mirrormon
USE master ;
GO
-- Grant connect permissions on endpoint to login account of witness & master.
GRANT CONNECT ON ENDPOINT::Mirroring TO [DOMAIN\mirrormon]
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_MONITOR_login]
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_ONE_login]
GO
SELECT * FROM sys.database_mirroring_endpoints;
--MONITORSVR:
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=WITNESS,
AUTHENTICATION = CERTIFICATE MONITORSVR_cert, ENCRYPTION = required)
GO
USE master ;
GO
-- Grant connect permissions on endpoint to login account of master & MIRRORSVR.
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_ONE_login]
GRANT CONNECT ON ENDPOINT::Mirroring TO [SQLSVR_TWO_login]
GO
SELECT * FROM sys.database_mirroring_endpoints;
5)===restore DB on MIRRORSVR: ===================================================
--MASTER:
BACKUP DATABASE dbname TO DISK = 'E:\MSSQL.1\MSSQL\Backup\dbname.bak' WITH FORMAT;
--MIRRORSVR:
CREATE LOGIN [DOMAIN\dbname_reader] FROM WINDOWS ;
CREATE LOGIN [DOMAIN\dbname_writer] FROM WINDOWS ;
NOTE: copy over the .bak file from above as well as at least one transaction log (.trn)
RESTORE DATABASE dbname FROM DISK = 'e:\foldername\dbname.bak' WITH NORECOVERY;
RESTORE DATABASE dbname FROM DISK = 'e:\foldername\dbname_backup_200803191415.trn' WITH NORECOVERY;
--MONITORSVR:
N/A
6)===Configure parters/witness: ===============================================
--MIRRORSVR:
ALTER DATABASE [dbname] SET PARTNER = 'TCP://fully.qualified.server.name:5022';
--MASTER:
ALTER DATABASE [dbname] SET PARTNER = 'TCP://fully.qualified.server.name:5022';
ALTER DATABASE [dbname] SET WITNESS = 'TCP://fully.qualified.server.name:5022';
--MONITORSVR:
N/A
NOTE: you may verify connections with:
select * from sys.dm_db_mirroring_connections;
*** REPEAT STEPS 5 & 6 for EACH DB to be MIRRORSVRTED! ***
7)===Optional steps: ==========================================================
--MASTER:
ALTER DATABASE [ ] SET PARTNER REDO_QUEUE 100MB;
ALTER DATABASE [ ] SET PARTNER TIMEOUT 30; -- (seconds)
--MIRRORSVR:
--MONITORSVR:
===RANDOM NOTES: ==============================================================
To view mirroring config on principle or mirror:
SELECT
DB_NAME(database_id) AS 'DatabaseName'
, mirroring_role_desc
, mirroring_safety_level_desc
, mirroring_state_desc
, mirroring_safety_sequence
, mirroring_role_sequence
, mirroring_partner_instance
, mirroring_witness_name
, mirroring_witness_state_desc
, mirroring_failover_lsn
,*
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
To get info on mirroring endpoints:
SELECT * FROM sys.database_mirroring_endpoints;
For spec on the various values:
http://technet.microsoft.com/en-us/library/ms190278.aspx
To get info on mirroring connections:
select * from sys.dm_db_mirroring_connections;
For spec on the various values:
http://msdn2.microsoft.com/en-us/library/ms189796.aspx
To change or set mirroring endpoint config:
ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = )
FOR database_mirroring (ROLE = ALL);
GO
To manage mirrors once created:
alter database fails over, must be run on priciple/master
alter database breaks mirrors
alter database suspends mirroring
alter database resumes mirroring for a suspended session (principle)
alter database sets the timeout (sec) (principle)
The more you are prepared, the less you need it.
December 9, 2008 at 1:56 pm
some of the script was cut off:
To change or set mirroring endpoint config:
ALTER ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = )
FOR database_mirroring (ROLE = ALL);
GO
To manage mirrors once created:
alter database set partner failover ;
= = > fails over, must be run on priciple/master
alter database set partner off ;
= = > breaks mirrors
alter database set partner suspend ;
= = > suspends mirroring
alter database set partner resume ;
= = > resumes mirroring for a suspended session
(principle)
alter database ;
= = > sets the timeout (sec) (principle)
The more you are prepared, the less you need it.
December 9, 2008 at 1:59 pm
Thanks for the quick reply Andrew!
On your reply you say "CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' ';"
When I run this on my DB to be mirrored, I get the error of "An error occurred during encryption."
From here it is unclear to me what I need to do to get this fixed. I know the service account was manually changed before, but no backup was made of the SMK. So, I guess I'm wondering what my options are in order to get past this hurdle and continue through the steps you've provided.
Thanks so much!
December 9, 2008 at 2:56 pm
Hi All,
I checked around my shop here and found that data is not being encrypted currently (or ever) using DbMKs so FORCE REGENERATE of the SMK would not pose a hefty threat. After doing the regeneration, I was able to make a backup of the SMK and create a key on the database to be mirrored. Now I believe I can run through the steps needed to get the mirroring fully setup.
Thanks again Andrew.
December 9, 2008 at 4:50 pm
Ahh premature confidence...
I'm having difficulties setting the partner servers via "ALTER DATABASE [db] SET PARTNER =" command. Does the "GRANT CONNECT..." command impact the "ALTER DATABASE [db] SET PARTNER=" command? If so, I'm unclear on which account the mirroring session will be using to communicate back and forth. This seems to be the last major piece of my puzzle.
Thanks so much in advance.
December 9, 2008 at 6:53 pm
yea, the scripts are not a total guide. For logins, a good way to go is to just use a single login for all servers. in this case, a SQL Server login, rather than a domain account. That way you only one.
I'd really suggest you take some time and really understand how it works, and more importantly, what to do when it stops working.
here are some guides:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
The more you are prepared, the less you need it.
December 10, 2008 at 9:03 am
Thanks for those links Andrew. The first one is the info that I've been trying to go through.
It says that there are essentially three steps and I don't think that they're detailed enough, at least not for me. I've got a single login created on each instance (same login). The certificates are created, the endpoints are created and started, the login has been granted connection rights to each server, but I can't setup the partner server (error is that The server network address "TCP://blah:5022" can not be reached or does not exist.) I can ping that server just fine using the fully qualified name. What am I missing?
Also, I understand the implications of not knowing what to do if mirroring stops working, but I can't even get it to start working, so it's a moot point for now. =) Once I can get it working in a test environment, I'll feel much more confident going through the various scenarios that could cause it to stop working.
Thanks again for your help!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply