2

I am trying to automate existing sql server database deployment automation using Jenkins.

One of the ways to automate is to include the SQL scripts (stored procedures, views, table creation) in a SQL server Database project using Visual Studio. Use MSBuild to build the project and deploy dacpac using SQLPackage.exe. However, the existing database has reference to other databases inside Stored Procedures/Views so I have import other databases inside the SQL Server Database project. Additionally, I am getting a lot of errors when extracting dacpac of existing databases. I assume this is due to existing code is not dacpac compatible and may require changes to be dacpac compatible.

Is there any other way to automate SQL server code deployment instead of using SQL Server database project?

Improve this question
2
  • this might be a broad question but I need a starting point to look for different options. I assume it would be useful for others who are looking for similar options. Commented May 24, 2019 at 9:46
  • 1
    I blogged about this here: schottsql.com/all-ssdt-articles - look for the "External References" article. I ended up using dacpacs for external DB references. Still have to clean up code to remove 3-part names for the current db, but it tends to work. We could then build/deploy without too much trouble. -- May be worth looking at Red-Gate's offering as well, but external references might still give you trouble there. Commented May 24, 2019 at 18:38

2 Answers 2

Reset to default
4

SSDT is a good option, however it will take a while for you and all developers to get used to it and understand what is offline development and state based deployments. You can read how SSDT works by yourself, however there are few advice for you:

  • For every database involved into your queries you need to create project
  • If you use other database in the code, then this project is supposed to be added as a reference
  • Instead of having 3/4 part names in the code (server.database.schema.name or database.schema.name) create synonyms for every single object and in the synonym use variables for the server and database names
  • Don't put logins, users and permissions into that project
  • Create publish profiles and take a look to all settings carefully (for example exclude users, logins, permissions and so on from deploying)
  • There are pre/post scripts where you can create some workarounds

Mostly people afraid of fully automated deployment to PROD using SSDT as the script is generated automatically and there is only very limit control on the final deployment script (to be fair, you can control pretty much everything using deployment contributors, but it is the topic of another discussion). Usually there is script review step between script generation and the deployment.

So, if you'll stick with SSDT the roardmap for you is:

  • Create projects for all databases used in the code
  • Create synonyms for all the objects in the project that are from other database with variables as the database and instance names (very important point)
  • Use replace across multiple files for all the external database and instances

HINT for replacement. There are several combinations of the object names in the code:

  • INSTANCE.DATABASE.SCHEMA.OBJECT
  • INSTANCE.DATABASE..OBJECT
  • INSTANCE...OBJECT
  • All other combinations with [] or "", for example [dbo].[SomeTable] or [dbo].SomeTable

So what you can do is to replace INSTANCE with $(InstanceName) across of all files, the [Database] with [$(DatabaseName)] and so on. Be creative and basically when you know what to do you can put pretty much any db to the SSDT in few hours with these tricks.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for the detailed instructions.
Regarding "Don't put logins, users and permissions into that project", where can I put "Grant Execute On [SP] To [User] As [dbo]" statement? I think I will need to create a script for the user and its login as well to compile this statement.
You can add it to post script
Ok, will it retain permission if I have to alter the stored procedure in future? (i.e. will it require grant permission permanently in the post script?)
In SSDT post script is something that is ALWAYS executed. So if you add this script there it must be idempotent.
|
2

An alternative to SSDT model-based projects is a migration script approach as discussed here.

Model based deployments need to validate dependencies during build, else you would run the risk of errors during deployment. Unfortunately, I know of no easy way to create a dacpac for other referenced databases that aren't dacpac-friendly without going through the effort of creating separate projects for referenced databases built from source. You could create a database project for referenced databases and use the SSDT import wizard to extract database the schema for the project source code and clean up the solution.

The complexity of this task will vary considerably depending on the number of cross-database and cross-server dependencies you have. It may be necessary to split databases into separate projects in order to avoid circular references.

Improve this answer

1 Comment

You can use the sqlpackage command line to extract dacpacs of other DBs to use as references. No need to build a project for them if you won't be putting those into source control. Can even strip them down with a little work if you only need a small number of objects inside.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.