Linked Server and Mirroring issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Linked Server and Mirroring issue

Author

Topic

hari_sql
Starting Member

22 Posts

Posted - 2010-12-30 : 04:12:46

Hi,

I have a DB 'NCAccounts' which is mirrored (Principal and Mirror in two separate servers). I have yet another DB in a 3rd server. In the 3rd server, I created a Linked Server as follows to connect to 'NCAccounts':

--Create Linked Server
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',
@srvproduct=N'.', @provider=N'SQLNCLI',
@provstr=N'Server=DBASE-1;Partner=DBASE-2',
@catalog=N'NCAccounts'

--Create login for linked server
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MyLinkedServer',@useself=N'False',@locallogin=N'Admin',@rmtuser=N'Admin',@rmtpassword=N'adminpwd'

The linked server was created, but on executing
"select * from MyLinkedServer.master.dbo.sysdatabases", the following error message was displayed:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'DBASE-1\admin'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "NCAccounts" requested by the login. The login failed.
OLE DB provider "SQLNCLI" for linked server "MyLinkedServer" returned message "Invalid connection string attribute".

At this point I noted that DBASE-1 was acting as the Mirror and DBASE-2 was acting as the Principal in the Mirror setup. So I recreated the Linked Server by interchanging the servers as:

@provstr=N'Server=DBASE-2;Partner=DBASE-1',
and it worked! (Please note that DBASE-1 and DBASE-2 have been interchanged here)

QUESTION: Won't Linked Server automatically consider and switch between the Primary and Partner (Mirror) DBs in the wake of a failover?

Thanks and Regards,
Hari_SQL.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-30 : 12:12:29

No, linked server won't recognize the mirror failover. Remember that mirroring is a database level technique, while linked server is server level.

Best you can do is to write a script to recreate the linked server (pointing to the mirror) that will be executed in the event of failover.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources