Do linked servers stop working after migrating the system dbs to a new sever?
The service master key is automatically generated the first time it is needed to encrypt a linked server password, credential, or database master key.
Does this mean that if I restore the system dbs onto a new server (as part of migration or recovery), then will the linked servers stop working (since the new server won't have the same service master key)?
It seems you're gonna have to generate the scripts for the linked servers as you can see on https://www.sqlservercentral.com/forums/topic/how-to-transfer-linked-servers-from-one-server-to-another-automatically , therefore, if you follow the https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql?view=sql-server-ver15 doc before executing the generated scripts on the new instance, the linked servers recreated will be encrypted with the same service master key from the old server.
According to what the https://sqlity.net/en/2368/service-master-key/ article says,
The service master key is a symmetric key stored in the master database. As such it can be found using the sys.symetric_keys catalog view in the that very database.
So, I'd say the linked servers won't stop working since the service master key would be restored along with the
masterdatabase. To make sure that's the way it works you could easily test the procedure:
- Backup the master database on the old instance;
- Generate the linked server scripts;
- Restore the master database on the new instance;
- Execute the scripts for the linked servers on the new instance;
- Test if the linked servers are still working.
Also, it's recommended to https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/back-up-the-service-master-key?view=sql-server-ver15 .