locked
How Sql writer works in Sql server2011 with HADR configuration. RRS feed

  • Question

  • Can anybody tell me the role of sql writer in Sql server2011  with HADR configuration. means how it makes the sync between two database primary and secondary..

    I want to know exactly wheather the sql writer is used only on the server which has the primary database instance or sql writer is used on the secondary databse also .incase the log file is trauncated on primary database which has to be reflected on the secondary database.

    please let me know with the internal architecture how the sync happens if you can .

    Monday, February 28, 2011 7:44 AM

Answers

  • With AlwaysOn (the formal name of what used to be referred to as HADRon), you will be able to do backups on any replica.  This applies to TSQL backups, VDI backups which use 3rd-party apps, and VSS/SQLWriter backups.

    One restriction which has not been lifted is that we do not (have never) suppored SQLWriter/VSS based log backups.  It's on my list, but we haven't gotten it written yet.  Since full database backups do not cause log truncation, that is not an issue here.

    In general though, when a backup is performed on a secondary, there is communication between it and the primary.  Specifically, at the beginning, the secondary informs the primary that a backup is starting, which causes the primary to take out the same lock which is used on local backups to serialize backups, so you don't have two log backups overlapping. 

    Then, when the backup is complete, the secondary informs the primary that the backup is complete, so the lock can be released, and the LSN for the backup.  If it's a log backup, the primary then uses it to update the last backup LSN, which is one of the inputs to log truncation.  This enables log truncation on the primary, and as the last backup LSN is replicated out to all other replicas, log truncation is enabled on them as well.

    Cheers,

    Kevin Farlee

    SQL Engine PM


    Microsoft SQL Server Storage Engine PM
    Thursday, March 10, 2011 10:22 PM

All replies

  • With AlwaysOn (the formal name of what used to be referred to as HADRon), you will be able to do backups on any replica.  This applies to TSQL backups, VDI backups which use 3rd-party apps, and VSS/SQLWriter backups.

    One restriction which has not been lifted is that we do not (have never) suppored SQLWriter/VSS based log backups.  It's on my list, but we haven't gotten it written yet.  Since full database backups do not cause log truncation, that is not an issue here.

    In general though, when a backup is performed on a secondary, there is communication between it and the primary.  Specifically, at the beginning, the secondary informs the primary that a backup is starting, which causes the primary to take out the same lock which is used on local backups to serialize backups, so you don't have two log backups overlapping. 

    Then, when the backup is complete, the secondary informs the primary that the backup is complete, so the lock can be released, and the LSN for the backup.  If it's a log backup, the primary then uses it to update the last backup LSN, which is one of the inputs to log truncation.  This enables log truncation on the primary, and as the last backup LSN is replicated out to all other replicas, log truncation is enabled on them as well.

    Cheers,

    Kevin Farlee

    SQL Engine PM


    Microsoft SQL Server Storage Engine PM
    Thursday, March 10, 2011 10:22 PM
  • Hi, Kevin.

    Maybe you can help me in the matter?

    1. I make a backup using VSS SQL Writer. And get the xml file that logicalPath = "Host \ Instance1". How to use VSS to restore database to "Host \ Instance2"?

    2. As I know SQL Writer allow restore with another name using SetRestoreOptions.

    "A requestor may need to restore a SQL database with a new name, especially if the database is to be restored side-by-side with the original database. This option can be specified by the requestor during the restore operation by setting a custom restore option as “New Component Name” = <”New Name”> using the VSS call IVssBackupComponents::SetRestoreOptions() (in the wszRestoreOptions parameter)."

    But this does not work. I could not find enough information about how SQL Writer parse xml and how I can rename,move to another instance(host) database. When I change in xml logicalPath or component name manually all works fine. But this is not good solution.

    Thanks,

    Dmitry.

    Friday, October 28, 2011 11:27 AM
  • Dmitry,

    You are correct that changing the host/instance by using the SetRestoreOptions() call does not work.  Currently, what is supported is giving the database itself a different name.

    I do have a list of Writer enhancements that I'm pitching to be done in the near future, and this (restore to alternate host/instance) is one of them.  As you've noted, hand-editing the XML accomplishes what you want, but this is not a tested or supported mechanism, so using it as your data protection plan probably doesn't make sense.

     

    Kevin


    Microsoft SQL Server Storage Engine PM
    • Proposed as answer by Dmitry83 Tuesday, November 1, 2011 6:10 AM
    Monday, October 31, 2011 9:22 PM
  • Hi, Kevin

    Thank you very much. I saved a lot of time.

    Only I have a couple of questions. The first is what the correct format for renaming databases with SetRestoreOptions because "New Component Name"=<"New Name"> does not work. And the second is how soon we can expect a SQL Writer with new features.

    Best regards,

    Dmitry.

    Tuesday, November 1, 2011 6:17 AM
  • SetRestoreOptions, specifying "New Component Name"=<"New name"> should work to rename a database within an instance.

    We have tests in our standard suites which do exactly that.  I took some time to read the source for the test driver and make sure that I verified what it's doing.

    The value for "New Name" should be just the new database name, not qualified with the instance name, so your option might look like:

    For component "SQLTEST_Server\testdb" "New Component Name" = "testdb_renamed"

     

    Hope that helps.

    Kevin


    Microsoft SQL Server Storage Engine PM
    Wednesday, November 2, 2011 5:42 PM
  • Hi Kevin ,

    can you please answer the follwings on denali ,

    Is it possible to get the port number on which Alwayson Listener is running on cluster using  cluster API .because my restriction is i can not refer to the Standalone instance ,I need to get this info from cluster database.

    Is there any changes done for denali to support VSS backup on the listener of the AlwaysOn group rather than using "node/instance" in componenet object in VSS.If the answer is yes to this question then how to deal with this case after a fail over.

     

    Thanks

    Arun

     

    Thursday, December 1, 2011 10:55 AM
  • Hello Kevin,

    Is it possible to restore database with rename side by side with original database on same instance? If I use SetRestoreOptions as described in previous answers I could restore DB with new name only if original does not exists on server. 

     

    Thanks,

    Ljubo

    Thursday, January 5, 2012 12:57 PM
  • You can restore a database which was backed up using SQLWriter side-by-side with the original on the same instance if you rename it, AND if you relocate the files to a new location.
    Microsoft SQL Server Storage Engine PM
    Monday, January 9, 2012 5:18 PM