Search code examples
sql-servervisual-studiosql-server-data-tools

SSDT with distributed database scenario

Currently we have started development of SQL server database using SQL Server Database Project. In future this database should be distributed into N different SQL server instances. Each instance should have a linked server references to each other instances and each database instance should have distributed partitioned view that referencing other instances of this database.

For testing purposes I created 2 different projects each holding linked server references and a copy of partitioned view ... but first project won't compile because of error

Error: SQL71561: View: [dbo].[Sample] has an unresolved reference to object [$(SAMPLESERVER_1)].[$(SampleDB)].[dbo].[T_Sample].

I read about database references but I think I can't use it because of circular referencing problem.

Can you point me to a possible solution(s) or SSDT is not applicable in distributed environments.

Solution

  • Bwa-ha-ha. SSDT and linked server ... Welcome! :) To be fair there is nothing complex when you know what to do, however it is big pain in the ... when you just start especially if you trying to move existing database to the SSDT project. IMHO linked servers must be described in the SSDT start page to save developers hours ...

    OK, now back to the question:

    1. Create linked database dacpac;

      • You can do that from Management Studio: RClick on db, Tasks -> Extract Data-Tier application. (if that would fail because of dependencies (and most probably it will fail)) then

      • Find location of the "SqlPackage.exe" (it depends on the version of SQL Server) file and run: "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\"SqlPackage.exe /Action:Extract /SourceDatabaseName:"DBNAME" /SourceServerName:server.url.com /TargetFile:"F:\FILENAME.dacpac" /SourcePassword:SecretPass /SourceUser:YourUser

    2. Add this dacpac as database reference Make sure you select Different Database and Server
    3. OPTIONAL: Create synonyms for every linked object
    4. Use this new object by using: [$(DbServer)].[$(DbName)].[dbo].[T_Sample]

    That's it. In conclusion I would like to add that SSDT is a great tool, however the first impression could be very bad ...