Linked server in SSDT project causes database drift

Question

I'm using SSDT (VS 2012) to maintain and deploy our database.

Recently we've added a linked server to the main database project and it deploys nicely the first time. The oncoming deploys to the same db-server / database fails due to database drift, giving the error:

SQL00000: Database has drifted from its registered data-tier application.

The object [LinkedServer] already exists in database with a different definition and will not be altered

NOTE: No changes has been made to the database when this error arises.

The linked server is configured as below (customer specific data removed):

EXEC sp_addlinkedserver   
       @server=N'LinkedServer', 
       @srvproduct=N'',
       @provider=N'SQLNCLI', 
       @datasrc=N'SrcPath';

The script file has Build Action set to Build, and is deployed as a part of the schema.

The publish profile we use has the setting "Block publish when database has drifted from registered version" set. Changing this setting is not an option because we want to stop publish if the database has drifted.

Answer 1

Did you add this as a pre-deploy script? If so, consider writing some logic around it to check whether it exists or not. I can't think why I'd deploy this as a "Build" script because it will try to do exactly the above every single time you deploy.