Post reply

can I change a setting in sys.servers?

  • January 10, 2012 at 1:00 pm

    #251258

    Hi,

    Have a question, but first..the scenario .

    We have two db servers, one is the primary (we’ll call server1) the other is mirrored (we’ll call server2). We just ‘failed over’ one of the databases on server1 to the mirrored server2 – for performance reasons – and we need to create a linked server - server2 - on server1 but it’s saying it already exist. We’re assuming this was created when the mirroring was set up, but it does NOT show under Server Object>Linked servers. When I run the sys.server, server2 shows up with is_linked =0 and is_remote_proc_transaction_promotion_enabled =0.

    Question. How can I turn these on? How can I change the setting to is_linked=1 and is_remote_proc_transaction_promotion_enabled =1?

    Need input please,

    D

  • January 10, 2012 at 1:14 pm

    #1431378

    sp_dropserver should help you

    http://msdn.microsoft.com/en-us/library/ms174310.aspx

  • January 10, 2012 at 1:25 pm

    #1431384

    yeah...I was kinda hoping there was another way. doing the sp_dropserver is going to muck up the mirroring. So can i simply stop the mirroring, run sp_dropserver then run sp_addlinkedserver - or use SSMS to add the linked server? then restart the mirroring?

  • January 10, 2012 at 1:34 pm

    #1431391

    I don't think so. This is a logged operation, so you can't do something out of band and then restart mirroring.

  • January 10, 2012 at 1:58 pm

    #1431400

    ugh....ok.

    Thanks for the quick reply.

    D

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply