SQL Server Performance Forum – Threads Archive

mirror error

In our sqlserver databases, we are using the thirdparty tool called idera sqlsafe for backups. And we need to restore that backup using the folowing command: EXEC [master].[dbo].[xp_ss_restore]
@database = ‘TestDBM’,
@filename = ‘D:dumpsTestDBM.BAK’,
@managementserver = ‘testserver’,
@backuptype = ‘Full’,
@encryptionpassword = ‘password’,
@withmove = ‘TestDBM C:MSSQLdataTestDBM.MDF’,
@withmove = ‘TestDBM_log C:MSSQLlogTestDBM_log.LDF’,
@recoverymode = null
— but while creating a mirroring i am getting an error :1418 , and am notable to figure it out why.can anyone help me out with this error. We are having the mirroring on server , which it has
windows 2003 sp2 (x64)
sqlserver sp2
thanks

You should restore the database with NORECOVERY for mirror setup….
So is your database restored with NORECOVERY or with RECOVERY?
MohammedU.
Moderator
SQL-Server-Performance.com

@recoverymode = null this above st. is used, i think if it’s null means it’s with no recovery

Most of the times default is with RECOVERY…
Check idera sqlsafe help…. MohammedU.
Moderator
SQL-Server-Performance.com

Run the following code…if you get the output READ_WRITE means your database restored with RECOVERY option…. SELECT DATABASEPROPERTYEX(‘pubs’, ‘Updateability’)
go
SELECT DATABASEPROPERTYEX(‘pubs’, ‘status’) MohammedU.
Moderator
SQL-Server-Performance.com

hi MohammedU
i am very thankful to you, for all your help… yes it’s with the recovery, so how can i change that for norecovery option

If it is native sql add WITH NORECOVERY options… For Idera sqlsafe I don’t know check its help…
MohammedU.
Moderator
SQL-Server-Performance.com

Yes i checked with them they
i have to add @recoverymode=’NORECOVERY’ in the lines.One more question, will the norecovery option take long time to restore the database??

With recovery should take longer than NORECOVERY because it has rollback uncommitted transactions and rollforward committed transactions…. From Book Online… Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY | NORECOVERY ] options: NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.
RECOVERY (the default) indicates that roll back should be performed after roll forward is completed for the current backup. Recovering the database requires that the entire set of data being restored (the roll forward set) is consistent with the database. If the roll forward set has not been rolled forward far enough to be consistent with the database and RECOVERY is specified, the Database Engine issues an error. MohammedU.
Moderator
SQL-Server-Performance.com

but it’s simple test database , it’s saying TestDBM(Restoring..) for the past 2 hour. Is there any issues

Check the restore throughput using SYSMON (PERFMON)… to see what is going on…
MohammedU.
Moderator
SQL-Server-Performance.com

but if we restore the database using norecovery option, it’ll be in restore state right?
since it’s an standby server , and still it will wait to restore the remaing log and database files….

Yes… MohammedU.
Moderator
SQL-Server-Performance.com

hi MohammedU, i am confused now.. sorry i am new to this mirroring..
if the database is in the restore state , then how can i perform the mirroring..
i am not able to create endpoints too…. please help me out…. or tell me how can i create database mirror ing…

Priyaram The mirror d/b is always be in restore mode and inaccessible. You can only access the principal d/b. If you have to access the data in the mirror d/b u have to create databse snapshot to view the data. Also make a note that database snapshots are only possible in Enterprise edition of SQL Server Satya

Well here is the code to create the endpoints on the principal and mirror servers if not exists(select * from sys.endpoints where type=4)
create endpoint DBMirrorEndPoint
state=started as TCP(Listener_Port=4040)
for database_mirroring(authentication=certificate PrincipalServerCert,
Encryption = REQUIRED,Role=All)
You can have the name of the endpoint as you like and it is also up to you on how to configure the port number which can be user for Miroring Use the name of the Server certificate as you like depending upon the server u r creating it like ‘PrincipalServercert’ or ‘MirrorServercert’ or whatever. Satya

i am getting the following error: Database Mirroring login attempt failed with error: ‘Connection handshake failed. There is no compatible encryption algorithm. State 22.’. [CLIENT: server ip address]

Didnt u try to create master key encryption using a password?

i didn’t get what ur asking .. sorry

Ok There are a lot of steps to do before you create endpoints. I was thinking that you were done with those and having problem with only the endpoints. The steps inorder are: 1. Create master key
2. Create certificate.
3. Backup the certificate.
4. Copy yhe certificates over n/w
5. Then create the endpoint. Have you done all the steps in this order? Satya

i am new to this mirroring i am going to create all the steps you mentioned above… i didn’t do anything ..I created all the databases in the server1(which is going to be the principal server) and i restored one databse in the server 2(which is going to be the mirror) with norecovery state. then i stared to mirror using management studio.., then i got the error i posted… please guide me how to setup the mirroring for the first time without witness server

Alright Implement the following script on the Principal Server: Look at each and every line before you implement the script to get a good idea of what each line of the code is doing: I have commented out the steps that are meant to undo the implemented ones. Donot run the commented lines unless you wish to undo mirroring. –Creating master key use master go if not exists(select * from sys.symmetric_keys where name=’##MS_DatabaseMasterkey##’) create master key encryption by password=’m!rror!ngtesting' go –drop master key if not exists (select 1 from sys.databases where [is_master_key_encrypted_by_server]=1) alter master key add encryption by service master key go –Creating certificate if not exists (select * from sys.certificates where name=’PrincipalServerCert’) create certificate PrincipalServerCert with subject=’Principal Server Certificate' go –drop certificate PrincipalServerCert –backup the certificate backup certificate PrincipalServerCert to file=’C

rincipalServercert.cer' –Copy the certificates over n/w –Check if mirroring end point exists if not exists(select * from sys.endpoints where type=4) create endpoint DBMirrorEndPoint state=started as TCP(Listener_Port=4040) for database_mirroring(authentication=certificate PrincipalServerCert, Encryption = REQUIRED,Role=All) –drop endpoint DBMirrorEndPoint –Create a login for mirror server and making the mirror connect to the principal server if not exists (select * from sys.syslogins where name=’MirrorServerUser’) create login MirrorServerUser with password = ‘password' –drop login MirrorServerUser if not exists(select * from sys.sysusers where name=’MoirrorServerUser’) create user MirrorServerUser; –drop user MirrorServerUser if not exists (select * from sys.certificates where name=’MirrorServerCert’) create certificate MirrorServerCert authorization MirrorServerUser from file=’C:MirrorServerCert.cer' –drop certificate MirrorServerPub grant connect on EndPoint:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />BMirrorEndpoint to MirrorServerUser if not exists (select 1 from sys.sysdatabases where name=’DBName’) alter database staging set partner=’TCP://servername:4040′ After you backup the certificate make sure you copy the backup file from Principal to Mirroe and do the same on Mirror Server This is just the script to implement on Principal server. I will post the Mirror Server script in the next post. Satya

thanks sathya…
i ‘ll try this first and let you know if there any error comes…

Mirror Server script: –Create master key use master go if not exists(select * from sys.symmetric_keys where name=’##MS_DatabaseMasterkey##’) create master key encryption by password=’m!rror!ngtesting' go –drop master key if not exists (select 1 from sys.databases where [is_master_key_encrypted_by_server]=1) alter master key add encryption by service master key go –Create certificate if not exists (select * from sys.certificates where name=’MirrorServerCert’) create certificate MirrorServerCert with subject=’Mirror Server Certificate' go –drop certificate MirrorServerCert –Backup certificate backup certificate MirrorServerCert to file=’C:MirrorServercert.cer' –Check if mirroring end point exists if not exists(select * from sys.endpoints where type=4) create endpoint DBMirrorEndPoint state=started as TCP(Listener_Port=4040) for database_mirroring(authentication=certificate MirrorServerCert, Encryption = REQUIRED,Role=All) –drop endpoint DBMirrorEndPoint –For mirror server to connect (creating the login and user) if not exists (select 1 from sys.syslogins where name=’PrincipalServerUSer’) create login PrincipalServerUser with password = ‘password' –drop login PrincipalServerUSer if not exists(select * from sys.sysusers where name=’PrincipalServerUser’) create user PrincipalServerUser; –drop user PrincipalServerUser if not exists (select * from sys.certificates where name=’PrincipalServerCert’) create certificate PrincipalServerCert authorization PrincipalServerUser from file=’C

rincipalServerCert.cer' —-drop certificate PrincipalServer grant connect on EndPoint:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />BMirrorEndpoint to PrincipalServerUser if not exists (select 1 from sys.sysdatabases where name=’DBName’) alter database DBName set partner=’TCP://Servername:4040′ One more very important thing is that you have to run the last step i.e. ‘alter d/b….set partner….’ first on Mirror Server and then the last step on the Principal Server. Good Luck Satya

One more important thing You have to speak to ur n/w team to make sure port 4040 or whatever u decide to use for mirroring is open

ok..i;ll check with the n/w team. while i try to backup the certificate to c: , i am getting an error. what type of permission i need to backup the certificate. "Msg 15240, Level 16, State 1, Line 1 Cannot write into file ‘C

rincipalServerCert.cer’. Verify that you have write permissions, that the file path is valid, and that the file does not already exist." no file is already existing on the c<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />rive

I guess SQL Server service account does not have enough previlages to create a file. Try changing the permissions of the SQL Server service account and that should work. Satya

i am logged in as sa account to create master key and certificate… i setup the services to start under windows domain account .e.g domainnameadmin
but when ever i selected to connect the management studio under windows authentication,
it’s connecting using my id domainnamepriyaram how can i resolve this permission issue

i gave the permission to root directort , then the backup worked’ thanks sathya —

Aren’t you local amin on the server? MohammedU.
Moderator
SQL-Server-Performance.com

yes i didn’t add that to local admin group, i did that it worked.
once i ran these scripts in the principal and the mirror server , shall i start mirroring using the management studio…

alter databse <dbname> set partnet = ‘TCP://&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rincipalservername>:4040′ this ran successfully on the mirror server. but i ran alter databse <dbname> set partnet = ‘TCP://<mirrorservername>:4040′ it’s failing for me with the error The server network address "TCP://<mirrorsservername>:4040" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

The message is very clear, check with the n/w team if the port is open or not Satya

thank you for all helping me out in the database mirroring every thing works fine. in the principal server , the database is MirrorTest (principal,synchronized) in the mirror server ,the database is MirrorTest (mirror,Synchronized/Restoring..)
is this right???
thanks

Exactly U r all set. Also study how to do a failover Satya

Hi Sathya One more thing i want to clarify in this..
In our server we are having 10 user databases , right now i have done this for only one user database, suppose if i want to do it for all the remaining 9 user databases means, i have to repeat all this steps for server and mirror again??
thank you

No All that you have done except the ‘alter d/b’ and creating the user are server level. So you only need to do those 2 steps now, create a user in the d/b and run the alter d/b statement for each d/b u wish to mirror. Satya

will the front end and all other applicatons which ever is referring to the principal database in the principal server, will chage automatically their setting to the mirror database in the mirror server when the failover occurs.??

You have to modify the connection string as shown below: "Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;" Look at this link to get complete info about D/B mirroring
Satya

Her is the link, forgot to post it in the previous post: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Hi sathya As per your note, i have to create user and alter database st. for another database to mirror.
ok, once i create the user , i have to authenticate the certificate to that user, then the alter st. also i have to the same port number right?? thanks

As per the link:

quote:
The next step is to establish database mirroring endpoints. Establishing endpoints requires that you have system administrator rights to the SQL Server instance. You must set up endpoints on each server that are specifically created as database mirroring endpoints. The easiest way to set up endpoints is to use the Configure Database Mirroring Security Wizard, which you can invoke by clicking the Configure Security button on the Mirroring page of the Database Properties dialog. The Configure Security dialogs will prompt you for computer names and port numbers, and optionally logins, before constructing and executing the CREATE ENDPOINT commands. You can also execute the CREATE ENDPOINT command using Transact-SQL. (See "How to: Create a Mirroring Endpoint (Transact-SQL)" in SQL Server Books Online.)

Each database endpoint must specify a unique port on the server. When working with SQL Server instances on separate machines, these port numbers can all be the same and the Configure Database Mirroring Security Wizard will automatically suggest port 5022 as the port. If any of the SQL Server instances are on the same machine, each instance must have a distinct port and the port numbers must be unique. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

hi sathya, i am not clear about the failover in that above link you sent. suppose in the application it’s querying across 2 databases in the principal server, but the only 1 database failed in the principal.. how to handle this situation… they told to define one connection per database…
so as per their statement if the one database fails and transferred to mirror, then how that query is going to work (since it;s querying one databse in mirror (which is failed) and other database in principal which doesnot fail… thanks

The database mirroring is associated per database and if your application needs both the databases then make sure to involve both of them, in order to preference you have to control the failover and until unless they are failed over you will not be able to connect the application to the databases., Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

hi sathya
i am testing the databse mirroring session. mirroring is set wit high safety without witness(synchronous) so i try to make the server and the database down in the principal server, then i ran the t-sql in partner alter database test1 set partner off , so that the mirror database will be available for use. so then i try to reestablish the mirror again for that databse , but it’s failing " alter database test1 set partner resume "
it’s givin me an error " test1 is not set for databse mirroring" i am not sure how to reestablish the mirroring session again thanks

quote:When safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgment from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service. (See the topic ‘Forced Service’ in SQL Server Books Online.)

Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

hi sathya, the forced service is for the high performance mode(transaction safety off).In my server it’s in high safety mode, for high safety mode, we can alter the database by ALTER DATABASE testdb1 SET PARTNER OFF
RESTORE DATABASE testdb1 WITH RECOVERY but if i run the above command , the mirroring session is broken, and when the pricipal server is up i have to re-establish the mirroring session again. so when i have to reestablish the mirroring session,how can i restore the database along with the transaction log… thanks

Your question is not clear The commands you mentioned there are meant to break the mirroring. You have to restore the most recent transaction log from the principal to mirror to make sure they are in sync before you start mirroring, else mirroring will not start. Hope this helps
Satya

Why not just stop the mirrroring and take complete backup from principal to mirror, then start the process to ensure it will not mismatch on the point. If you are using mirroring then do not worry about tlogs and also it depends upon the mode of mirroring used. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Satya Your point is also a good one, I tried the same, but in situations where you have a large d/b and the full backup taking a long time (and of course it takes almost equal amount time during restore), I had new transactions happening during that long period of full backup/restore that caused mismatch. In such situations T-log backup relatively takes less amount of time helping the d/b’s to get in synch. Am I right? Thanks
Satya

I don’t think so if you have such option then anyhow the DB mirroring is taking care of such options, they will not be any issues as I don’t have any my end managaing half TB sized database with similar setup. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>