Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"

pinocchio



a different scenario leads to the same problem, the error message:
Could not find "Standby" in sysservers. Run "sp_addlinkedserver" to add the server to sysservers"

I browsed the thread for the same error message and the solution doesn't applies to me. Here is my system settings. we have a kind of backup system. a primary server is set up to copy the database files to a standby server. when the primary is off, the standby server will take over primary's name and ip. and our application runs on the standby as if in the primary. in both machine the odbc connection are set to point the primary server.

this setup works well in SQL server 2000 for both primary and standby servers, but it gives the error message on SQL 2005 in the standby server. I noticed that this is because the application opens an empty table. after inserting a record by hand, the error has gone away.

I know it doesn't make too much sense, but it 's what I observed. Could anyone give me some input how could this happens and what' s the solution

oj


While both servers are running they must have unique hostname and IP. Thus, it would be necessary to create a linked server on the primary for the "standby" server. This would allow the primary to access/push the data to the standby server until such time you need to "promote" the standby to be the primary. To promote the standby, it would be as simple as rename it. The following article should help with that.
http://msdn2.microsoft.com/en-us/library/ms143799.aspx

Please note that, you can no longer allow to modify system tables in sql2k5.

pinocchio

I really appreciate your response. bu the setting is little more complex than that.

we do promote the standby to be the primary by assigning the primary's ip and change its network resolution name, but the computer name is not actually changed.

Another point is that during the promotion, the whole database files (under Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data) are copied to the standby. So I am assuming that the sqlserver itself has all the login/security settings for the primary.

The sqlserver is using windows authentication, two local accounts: "primary\myappuser" and "standby\myappuser" are setup in sqlserver specific for specific my application.

after revisiting all these settings, I wonder something wrong with the database authentication. Since the standby's computer name is not really changed, and the sqlserver thinks itself to be hosted in the primary, and my application is trying to login with "standby\myappuser" account.

Since this setting works in sqlserver 2000, I wonder the change in 2005 might not be compatible somewhere

oj

If you copy the entire \data folder (i.e. everything including master and other system databases), then the @@servername will not match serverproperty('servername'). Also, if your sqlserver services are started up under different accounts, the permissions granted to the files will be a problem.

Here are things to look up:
1. http://msdn2.microsoft.com/en-us/library/ms143504.aspx
2. http://support.microsoft.com/kb/283811
3. http://msdn2.microsoft.com/en-us/library/ms174411.aspx

pinocchio

Problem Solved!

From your hint and advice, I changed the replication script to copy only application data instead of the whole database .mdf files. In this way, when the standby is promoted, it still has the original settings.

Thanks oj, your knowledge and experience on sql2005 helps a lot.

oj

You're welcome. Please do close the post. Cheers.