SQL Server Performance

mirror error

Discussion in 'SQL Server 2005 Database Mirroring' started by priyaram, Mar 28, 2007.

  1. priyaram New Member

    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
  2. MohammedU New Member

    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
  3. priyaram New Member

    @recoverymode = null

    this above st. is used, i think if it's null means it's with no recovery
  4. MohammedU New Member

    Most of the times default is with RECOVERY...
    Check idera sqlsafe help....

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. MohammedU New Member

    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
  6. priyaram New Member

    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
  7. MohammedU New Member

    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
  8. priyaram New Member

    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??
  9. MohammedU New Member

    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
  10. priyaram New Member

    but it's simple test database , it's saying TestDBM(Restoring..) for the past 2 hour.

    Is there any issues
  11. MohammedU New Member

    Check the restore throughput using SYSMON (PERFMON)... to see what is going on...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  12. priyaram New Member

    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....
  13. MohammedU New Member

    Yes...

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  14. priyaram New Member

    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...
  15. satya.sqldba New Member

    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
  16. satya.sqldba New Member

    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
  17. priyaram New Member

    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]
  18. satya.sqldba New Member

    Didnt u try to create master key encryption using a password?
  19. priyaram New Member

    i didn't get what ur asking .. sorry
  20. satya.sqldba New Member

    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
  21. priyaram New Member

    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
  22. satya.sqldba New Member

    Alright<br /><br /> Implement the following script on the Principal Server:<br /><br />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:<br /><br />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.<br /><br /><br /><br />--Creating master key <br />use master <br />go<br />if not exists(select * from sys.symmetric_keys where name='##MS_DatabaseMasterkey##')<br />create master key encryption by password='m!rror!ngtesting'<br />go<br /><br />--drop master key <br /><br />if not exists (select 1 from sys.databases where [is_master_key_encrypted_by_server]=1)<br />alter master key add encryption by service master key<br />go<br /><br />--Creating certificate<br />if not exists (select * from sys.certificates where name='PrincipalServerCert')<br />create certificate PrincipalServerCert<br />with subject='Principal Server Certificate'<br />go<br /><br />--drop certificate PrincipalServerCert<br /><br /><br />--backup the certificate<br />backup certificate PrincipalServerCert to file='C:principalServercert.cer'<br /><br />--Copy the certificates over n/w<br /><br />--Check if mirroring end point exists<br />if not exists(select * from sys.endpoints where type=4)<br />create endpoint DBMirrorEndPoint<br />state=started as TCP(Listener_Port=4040)<br />for database_mirroring(authentication=certificate PrincipalServerCert,<br /> Encryption = REQUIRED,Role=All)<br /><br />--drop endpoint DBMirrorEndPoint<br /><br />--Create a login for mirror server and making the mirror connect to the principal server<br />if not exists (select * from sys.syslogins where name='MirrorServerUser')<br />create login MirrorServerUser with password = 'password'<br /><br />--drop login MirrorServerUser<br /><br />if not exists(select * from sys.sysusers where name='MoirrorServerUser')<br />create user MirrorServerUser;<br /><br />--drop user MirrorServerUser<br /><br />if not exists (select * from sys.certificates where name='MirrorServerCert')<br />create certificate MirrorServerCert authorization MirrorServerUser<br />from file='C:MirrorServerCert.cer'<br /><br />--drop certificate MirrorServerPub<br /><br />grant connect on EndPoint:<img src='/community/emoticons/emotion-2.gif' alt=':D' />BMirrorEndpoint to MirrorServerUser<br /><br />if not exists (select 1 from sys.sysdatabases where name='DBName')<br /><br />alter database staging<br />set partner='TCP://servername:4040'<br /><br />After you backup the certificate make sure you copy the backup file from Principal to Mirroe and do the same on Mirror Server<br /><br />This is just the script to implement on Principal server. I will post the Mirror Server script in the next post.<br /><br />Satya<br /><br /><br /><br /><br /><br /><br /><br /><br />
  23. priyaram New Member

    thanks sathya...
    i 'll try this first and let you know if there any error comes...
  24. satya.sqldba New Member

    Mirror Server script:<br /><br />--Create master key<br />use master <br />go<br />if not exists(select * from sys.symmetric_keys where name='##MS_DatabaseMasterkey##')<br />create master key encryption by password='m!rror!ngtesting'<br />go<br /><br />--drop master key<br /><br />if not exists (select 1 from sys.databases where [is_master_key_encrypted_by_server]=1)<br />alter master key add encryption by service master key<br />go<br /><br />--Create certificate<br />if not exists (select * from sys.certificates where name='MirrorServerCert')<br />create certificate MirrorServerCert<br />with subject='Mirror Server Certificate'<br />go<br /><br />--drop certificate MirrorServerCert<br /><br /><br />--Backup certificate<br />backup certificate MirrorServerCert to file='C:MirrorServercert.cer'<br /><br />--Check if mirroring end point exists<br />if not exists(select * from sys.endpoints where type=4)<br />create endpoint DBMirrorEndPoint<br />state=started as TCP(Listener_Port=4040)<br />for database_mirroring(authentication=certificate MirrorServerCert,<br /> Encryption = REQUIRED,Role=All)<br /><br />--drop endpoint DBMirrorEndPoint<br /><br />--For mirror server to connect (creating the login and user)<br />if not exists (select 1 from sys.syslogins where name='PrincipalServerUSer')<br />create login PrincipalServerUser with password = 'password'<br /><br />--drop login PrincipalServerUSer<br /><br />if not exists(select * from sys.sysusers where name='PrincipalServerUser')<br />create user PrincipalServerUser;<br /><br />--drop user PrincipalServerUser<br /><br />if not exists (select * from sys.certificates where name='PrincipalServerCert')<br />create certificate PrincipalServerCert authorization PrincipalServerUser<br />from file='C:principalServerCert.cer'<br /><br />----drop certificate PrincipalServer<br /><br />grant connect on EndPoint:<img src='/community/emoticons/emotion-2.gif' alt=':D' />BMirrorEndpoint to PrincipalServerUser<br /><br />if not exists (select 1 from sys.sysdatabases where name='DBName')<br /><br /><br />alter database DBName<br />set partner='TCP://Servername:4040'<br /><br />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.<br /><br /> Good Luck<br /> Satya<br /><br /><br />
  25. satya.sqldba New Member

    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
  26. priyaram New Member

    ok..i;ll check with the n/w team.<br />while i try to backup the certificate to c: , i am getting an error.<br />what type of permission i need to backup the certificate.<br /><br />"Msg 15240, Level 16, State 1, Line 1<br />Cannot write into file 'C:principalServerCert.cer'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist."<br /> <br /><br />no file is already existing on the c<img src='/community/emoticons/emotion-2.gif' alt=':d' />rive
  27. satya.sqldba New Member

    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
  28. priyaram New Member

    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
  29. priyaram New Member

    i gave the permission to root directort , then the backup worked'

    thanks sathya

    ---
  30. MohammedU New Member

    Aren't you local amin on the server?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  31. priyaram New Member

    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...
  32. priyaram New Member

    alter databse &lt;dbname&gt;<br />set partnet = 'TCP://&lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rincipalservername&gt;:4040'<br />this ran successfully on the mirror server.<br /><br />but i ran<br />alter databse &lt;dbname&gt;<br />set partnet = 'TCP://&lt;mirrorservername&gt;:4040'<br /><br />it's failing for me with the error<br />The server network address "TCP://&lt;mirrorsservername&gt;: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.<br />
  33. satya.sqldba New Member

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

    Satya
  34. priyaram New Member

    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

  35. satya.sqldba New Member

    Exactly

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

    Satya

  36. priyaram New Member

    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
  37. satya.sqldba New Member

    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
  38. priyaram New Member

    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.??
  39. satya.sqldba New Member

    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
  40. satya.sqldba New Member

  41. priyaram New Member

    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
  42. satya Moderator

    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.
  43. priyaram New Member

    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
  44. satya Moderator

    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.
  45. priyaram New Member

    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

  46. satya Moderator

    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.
  47. priyaram New Member

    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
  48. satya.sqldba New Member

    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
  49. satya Moderator

    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.
  50. satya.sqldba New Member

    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

  51. satya Moderator

    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.

Share This Page