Mirrored linked server failover.

We have a mirrored db setup, with a third server that needs to be linked to this mirrored setup as a linked server. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

It's not clear which way you are creating the linked server:

If it is from the primary->third server then you will need to create the linked server on both the principal and mirror since it does not automatically failover (it's defined at the instance level and not the database level).

If it is from the third server->primary then ensure you set the failover partner property in the linked server and that the default database is specified (catalog in provider speak). For example

EXEC master.dbo.sp_addlinkedserver

@.server = N'matthol11',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.catalog=N'test',

@.provstr=N'Server=matthol11;FailoverPartner=matthol6;'

select*from matthol11.test.dbo.test

Regards,

Matt

Mirrored linked server failover.

We have a mirror setup, with a third server that needs to be linked to this mirrored setup. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

Andre,

You can specify the partner name in the linked server server properties and the linked server will automatically failover from the principal to the mirror if the principal is failed over.

Regards,

Matt Hollingswort

Sr. Program Manager

Microsoft SQL Server

sql

Mirrored linked server failover.

We have a mirrored db setup, with a third server that needs to be linked to this mirrored setup as a linked server. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

It's not clear which way you are creating the linked server:

If it is from the primary->third server then you will need to create the linked server on both the principal and mirror since it does not automatically failover (it's defined at the instance level and not the database level).

If it is from the third server->primary then ensure you set the failover partner property in the linked server and that the default database is specified (catalog in provider speak). For example

EXEC master.dbo.sp_addlinkedserver

@.server = N'matthol11',

@.srvproduct=N'',

@.provider=N'SQLNCLI',

@.catalog=N'test',

@.provstr=N'Server=matthol11;FailoverPartner=matthol6;'

select * from matthol11.test.dbo.test

Regards,

Matt

Mirrored linked server failover.

We have a mirror setup, with a third server that needs to be linked to this mirrored setup. The link server setup only allows you to specify the primary server. How will the linked server setup know what the secondary server is? Thanks

Andre,

You can specify the partner name in the linked server server properties and the linked server will automatically failover from the principal to the mirror if the principal is failed over.

Regards,

Matt Hollingswort

Sr. Program Manager

Microsoft SQL Server