Best practices for SQL Server Database Projects

• Scott In Sydney

  Hall of Fame

  Points: 3331

  More actions

  March 17, 2019 at 11:23 pm

  #416882

  Hi,

  We have what is likely a typical environment:

  • Dev Server
  • Prod Server
  • (Mainly) same databases across servers
  • Differences between the databases, and the need to deploy "stuff" from Dev to Prod (and occasionally from Prod to Dev)

  
  For my own development on my current project, the number of views, stored processes, functions, etc. are becoming unwieldy (100+), and I need a better approach to maintain code and synchronize environments.

  To that end, I'm trying to learn SQL Server Database Projects. 

  I have a database RLDXHosp, and have done the following:

  • Created a blank solution named RLDXHosp.
  • Added two SQL Server Database Projects Dev_RLDXHosp and Prd_RLDXHosp
  • Imported the appropriate database for the two projects.

  I have these questions:

  1. If you have any good references, websites, etc. for SQL Server Database Projects, please let me know.  I did search these forums for "SQL Server Database Projects" (include the double quotes!), and read all 33 hits.  This looked promising, but I couldn't find Parts 1 and 3:  http://www.sqlservercentral.com/blogs/ed-elliotts-sql-developer-blog/2016/01/06/what-is-ssdt-part-2-more-reasons-to-bother/.  I'll hit YouTube tonight when I get home :-).
  2. Is it good practice to include Dev and Prod in the same solution, or should I have separate solutions with a single environment?  Because...
  3. When I RMB a project and select Build, SSDT builds both projects.  But at least I can filter the error messages in the Error List window to a single project.
  4. Both databases have synonyms pointing to a third database.  So I am getting a ton of messages such as:

     Error        SQL71501: Computed Column: [ext].[vwEPISODE_ATS].[facility_identifier] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[EPISODE_ATS].[facility_identifier] or [dbo].[EPISODE_ATS].[src]::[facility_identifier].

     This view is fine in SSMS and in the database.  It is not a complex view, it's just a single table - the only complexity is referencing a synonym.

  5. I think this is because I need to add a database reference for the third database to the project(s).  But AFAIK I then have to edit all my code to add the database reference, i.e. $(somedatabase).  Is there a way to automate this?  For example, drop and recreate the projects, add the database reference first, then import?  Will SSDT automatically add the $(somedatabase) reference to the code during import?
  6. Is there a way to reimport the database after I've imported, or do I need to drop and recreate the project in order to reimport?
  7. Do I need to have a clean build before I can deploy (publish?) back to the database?  And does it need to be clean across both projects?
  8. Can I deploy from Dev to Prod and vice versa?  (I assume I can; a better question is how can I do this?)

  I've got 100's of views/sp's/functions/synonyms I need to deploy from dev to prod.  I see the potential for SQL Server Database Projects, but am having a hard time wrapping my head around how they work.

  Apologies for the noob questions.  I'm happy to do my own research if you can point me in the right direction. 

  Thanks...