none
mirroring RRS feed

  • Question

  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    Friday, June 20, 2014 1:51 PM

Answers

  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    1.No you are not correct it will copy function, stored procedures and other database related objects it wont transfer logins, jobs DTS packages. Now what makes you think other wise any specific reason ?

    2 Although both are DR technologies Mirroring is different from replication.

    3. Once Mirroring established you cannot do anything on Mirror database because it is always in restoring state replaying transaction logs sent by principal to sync with it. Before configuring mirroring you need to make sure you restore full and log backup of database on mirror with No recovery option.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 1:59 PM
  • I initially thought the mirrored server is not up to date with prod because I thought it only copies table data not table field changes, new tables, stored procs or views...
    Now I believe, there is no need to restore prod database backup to mirrored server because it has everyting as same as prod. The only thing is that I have to manually update the jobs and server logins on the mirrored server.

    Bingo You got it. Thats absolutely correct .For database mirroring detailed scenarios and all possible configuration please refer below it was written for SQL 2005 but holds good for subsequent editions as well.There is whitepaer embedded in last please download it and refer as and when required

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 3:02 PM

All replies

  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    1.No you are not correct it will copy function, stored procedures and other database related objects it wont transfer logins, jobs DTS packages. Now what makes you think other wise any specific reason ?

    2 Although both are DR technologies Mirroring is different from replication.

    3. Once Mirroring established you cannot do anything on Mirror database because it is always in restoring state replaying transaction logs sent by principal to sync with it. Before configuring mirroring you need to make sure you restore full and log backup of database on mirror with No recovery option.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 1:59 PM
  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    1.No you are not correct it will copy function, stored procedures and other database related objects it wont transfer logins, jobs DTS packages. Now what makes you think other wise any specific reason ?

    2 Although both are DR technologies Mirroring is different from replication.

    3. Once Mirroring established you cannot do anything on Mirror database because it is always in restoring state replaying transaction logs sent by principal to sync with it. Before configuring mirroring you need to make sure you restore full and log backup of database on mirror with No recovery option.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Regarding point number 3.
    1- Are you sure it also transfers the sps, views and tables (even if a column field type is changed)?
    3- If we stop mirroring then is it poossible to restore the prod backup to that mirroring server and then re-start the mirroing again?

    Thanks

    Friday, June 20, 2014 2:06 PM
  • Regarding point number 3.
    1- Are you sure it also transfers the sps, views and tables (even if a column field type is changed)?
    3- If we stop mirroring then is it poossible to restore the prod backup to that mirroring server and then re-start the mirroing again?

    Thanks

    YES I am sure, this is what High  avaiability technology is all about. Suppose you create table and insert some data and also create procedure in database. Now his would generate some logs , information about this would be written in log file , this information in form of log will be moved to Mirror server and replayed to create table and proc exactly as in Principal Database. Now again what makes you think otherwise ?

    If you take my advise that would not be required at first place but if you are testing then Yes you need to break mirroring by running below command on Mirror database.

    --This code will break mirroring and bring Mirror database online

    ALTER DATABASE DB_NAME SET PARTNET OFF GO RESTORE DATABASE DB_NAME WITH RECOVERY

    After this you can restore backup of principal database on Mirror


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles



    Friday, June 20, 2014 2:15 PM
  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    1.No you are not correct it will copy function, stored procedures and other database related objects it wont transfer logins, jobs DTS packages. Now what makes you think other wise any specific reason ?

    2 Although both are DR technologies Mirroring is different from replication.

    3. Once Mirroring established you cannot do anything on Mirror database because it is always in restoring state replaying transaction logs sent by principal to sync with it. Before configuring mirroring you need to make sure you restore full and log backup of database on mirror with No recovery option.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Regarding point number 3.
    1- Are you sure it also transfers the sps, views and tables (even if a column field type is changed)?
    3- If we stop mirroring then is it poossible to restore the prod backup to that mirroring server and then re-start the mirroing again?

    Thanks

    Mirroring of course uses an exact copy of the primary database.

    It starts with a full backup, and then keeps replaying transaction records. So of course everything in the database is included.

    Yes you can set it up again and again if you wish so. The question would be: why? Why not letting it run?


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Friday, June 20, 2014 2:16 PM
  • Regarding point number 3.
    1- Are you sure it also transfers the sps, views and tables (even if a column field type is changed)?
    3- If we stop mirroring then is it poossible to restore the prod backup to that mirroring server and then re-start the mirroing again?

    Thanks

    YES I am sure, this is what High  avaiability technology is all about. Suppose you create table and insert some data and also create procedure in database. Now his would generate some logs , information about this would be written in log file , this information in form of log will be moved to Mirror server and replayed to create table and proc exactly as in Principal Database. Now again what makes you think otherwise ?

    If you take my advise that would not be required at first place but if you are testing then Yes you need to break mirroring by running below command on Mirror database.

    --This code will break mirroring and bring Mirror database online

    ALTER DATABASE DB_NAME SET PARTNET OFF GO RESTORE DATABASE DB_NAME WITH RECOVERY

    After this you can restore backup of principal database on Mirror


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles



    I see. In that case, what is the point of restoringthe production backup to the mirrored server since the mirrored server has everything a backup can have?

    Thanks

    Friday, June 20, 2014 2:43 PM
  • hello,
    we have mirroring in place.
    it takes table data from sql server server1 to server2.
    It does not seem to copy the things such as stored procs, functions, views, etc.
    1- Is it possible to set this mirroring so that it also copies what I mentioned above?
    2- Is mirroring the same as replication?
    3- is it possible to restore the latest backup of the production server and restore it into the mirroring server? this is so that the mirroring server will have all the production stored procs, views, etc.? this then avoids running the production changed stored proc or view in the mirroring server one by one.

    Thanks

    1.No you are not correct it will copy function, stored procedures and other database related objects it wont transfer logins, jobs DTS packages. Now what makes you think other wise any specific reason ?

    2 Although both are DR technologies Mirroring is different from replication.

    3. Once Mirroring established you cannot do anything on Mirror database because it is always in restoring state replaying transaction logs sent by principal to sync with it. Before configuring mirroring you need to make sure you restore full and log backup of database on mirror with No recovery option.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Regarding point number 3.
    1- Are you sure it also transfers the sps, views and tables (even if a column field type is changed)?
    3- If we stop mirroring then is it poossible to restore the prod backup to that mirroring server and then re-start the mirroing again?

    Thanks

    Mirroring of course uses an exact copy of the primary database.

    It starts with a full backup, and then keeps replaying transaction records. So of course everything in the database is included.

    Yes you can set it up again and again if you wish so. The question would be: why? Why not letting it run?


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    I initially thought the mirrored server is not up to date with prod because I thought it only copies table data not table field changes, new tables, stored procs or views...
    Now I believe, there is no need to restore prod database backup to mirrored server because it has everyting as same as prod. The only thing is that I have to manually update the jobs and server logins on the mirrored server.

    Friday, June 20, 2014 2:48 PM
  • ...

    I initially thought the mirrored server is not up to date with prod because I thought it only copies table data not table field changes, new tables, stored procs or views...
    Now I believe, there is no need to restore prod database backup to mirrored server because it has everyting as same as prod. The only thing is that I have to manually update the jobs and server logins on the mirrored server.

    That's exactly the point.

    Only the certain user-database is synchronized, everything else outside at server-level needs to be taken care of by other means.

    So you got the concept, very good. :)

    Here you can also read more on mirroring: http://msdn.microsoft.com/en-us/library/ms189852.aspx


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Proposed as answer by Ashwin Menon Friday, June 20, 2014 3:07 PM
    Friday, June 20, 2014 2:52 PM
  • I initially thought the mirrored server is not up to date with prod because I thought it only copies table data not table field changes, new tables, stored procs or views...
    Now I believe, there is no need to restore prod database backup to mirrored server because it has everyting as same as prod. The only thing is that I have to manually update the jobs and server logins on the mirrored server.

    Bingo You got it. Thats absolutely correct .For database mirroring detailed scenarios and all possible configuration please refer below it was written for SQL 2005 but holds good for subsequent editions as well.There is whitepaer embedded in last please download it and refer as and when required

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Friday, June 20, 2014 3:02 PM
  • I initially thought the mirrored server is not up to date with prod because I thought it only copies table data not table field changes, new tables, stored procs or views...
    Now I believe, there is no need to restore prod database backup to mirrored server because it has everyting as same as prod. The only thing is that I have to manually update the jobs and server logins on the mirrored server.

    Bingo You got it. Thats absolutely correct .For database mirroring detailed scenarios and all possible configuration please refer below it was written for SQL 2005 but holds good for subsequent editions as well.There is whitepaer embedded in last please download it and refer as and when required

    http://technet.microsoft.com/en-gb/library/cc917680.aspx


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles

    Lovely.

    Many thanks

    Friday, June 20, 2014 3:20 PM