SQL Server Databases

Last updated

There are a variety of ways for Octopus Deploy to deploy to SQL Server. Octopus Deploy integrates with many third-party tools, both free and commerical. This section will provide a broad outline of approaches, tooling, and recommended configuration.

Approaches to database change management

There are two main approaches to deploying databases.

  1. A model-driven approach, where you define the desired state of your database. During deployment, a tool will compare the desired state with the target database. Using that comparison it will generate a delta script.

Redgate's DLM Automation Suite, Microsoft's DacPac, and Microsoft's Entity Framework Migrations use the model-driven approach.

  1. A change-driven approach, where schema changes are manually written and only run once. The target database keeps track of which scripts already ran.

Redgate's ReadyRoll, DbUp, and RoundhousE use the change-driven approach.

There are pros and cons to either approach as well as the tools themselves. It is important for your team to research the tools and determine the best one for you.

Tentacle Installation Recommendations

Deploying an IIS Web Application or a Windows Service is very straight-forward. Install the Tentacle on the server to be deployed to. SQL Server is different. Production SQL Servers are typically clusters or high-availability groups. They comprise more than one node hidden behind a VIP or virtual IP Address.

For high-availability groups, there is an active node and a passive node. In this case, installing a Tentacle on each node will not work. Octopus Deploy will see multiple Tentacles and attempt to deploy to both nodes.

SQL PaaS, such as AWS RDS or Azure's SQL, will not allow the installation of Tentacles on SQL Server.

All the tools mentioned above connect to SQL Server using port 1433 and run one or more scripts. They do not need to be installed directly on SQL Server. Nor do they need to be run directly on SQL Server. They will work as long as they run on any machine with a direct connection and port 1433 open.

Also, Windows authentication is the often the preferred way to authenticate. A recommended security practice is the principle of least privilege. The account used by the website to connect to SQL Server should have restricted permissions. For example, the website uses stored procedures, the account would only have permissions to execute those stored procedures. Whereas, the account used for deployments needs elevated permissions. This is because that account needs to make schema changes.

Most of the tooling from above requires it to be installed somewhere. It is important the same version is used across all environments. This prevents any uncertainty during deployments.

Finally, it is good security practice to have a different deployment account per environment. That deployment account only has permissions to make schema changes in their environment. An account used to change a test environment should not be able to change production.

With all that in mind, a "jump box" is where Tentacles should be installed. The jump box sits between Octopus Deploy and the SQL Server VIP. The Tentacle is running as a service account with the necessary permissions to make schema changes. The tooling chosen for database deployments is installed on the jump box.

In the event of multiple domains, a jump box would be needed per domain. This might be seen where there is a domain in a local infrastructure and another domain in a cloud provider such as Azure. As long as port 10933 is open (for a listening Tentacle) or port 443 (for a polling Tentacle) Octopus will be able to communicate to the jumpbox.

It is possible to install many tentacles on a single server. Please read here for more information.

Database Deployment Permissions

The level of permissions required to automate database deployments is tricky. There is a fine line between functionality and security. There is no single magic bullet. It will be up to you and your security team to discuss. With that said, below are some considerations around permissions and a couple of recommendations.

Application Account Permissions

Applications should run under their own accounts with the least amount of rights. Each environment for each application should have their own account.

Having separate accounts for each environment can make automated database deployments very tricky. Which account should be stored in source control? All of them or none of them? None of them. Assign permissions to roles. Attach the correct user for the environment to that role.

Deployment Permission Considerations

The account used to make schema changes requires elevated permissions. Because of that, create a special account to handle database deployments. Do not use the same account used by an IIS Web Application.

The level of elevated permissions is up to you. More restrictions placed on the deployment account means more manual steps. Deployments will fail due to missing or restricted permissions. Octopus will provide the error message to fix the issue. It will need a manual intervention to resolve the issue. It is up to you to decide which is best.

First, decide what the deployment account should have the ability to do at the server level. From there, research which server roles are applicable. Microsoft has provided a chart of the server roles and their specific permissions.

Next, decide what permissions the deployment account can have at the database level. Again, Microsoft has provided a chart of the database roles and their specific permissions.

With those two charts in mind, below are some recommended permissions sets.

Fully Automated Database Deployments Permission Recommendation

Following DevOps principles, everything that can be automated should be automated. This includes creating databases, user management, schema changes and data changes. Octopus Deploy plus the third-party tool of your choice can handle that. The deployment account should have these roles assigned:

  • Server Permissions
    • dbcreator -> ability to create new databases
    • securityadmin -> ability to create new users and grant them permissions (you will need a check in place to ensure it doesn't grant random people sysadmin roles)
  • Database Permissions
    • db_ddladmin -> can run any Data Definition Language (DDL) command in a database.
    • db_datareader -> can read all the data from all user tables
    • db_datawriter -> can add, delete, or change data from all user tables
    • db_backupoperator -> can backup the database
    • db_securityadmin -> modify role membership and manage permissions
    • db_accessadmin -> can add or remove access to the database for logins
    • Can View Any Definition

Be sure to assign the deployment account those database roles in the model database. That is the system database used by SQL Server as a base when a new database is created. This means the deployment account will be assigned to those roles going forward.

Fully Automated Database Deployments Permission Recommendation

Security Admins should be treated the same as System Admins, as they can grant permissions at the server level. For security purposes, it is common to see that role restricted. In that case, below are the recommended permissions. It can do everything except create a new SQL Login.

  • Server Permissions
    • dbcreator -> ability to create new databases
  • Database Permissions
    • db_ddladmin -> can run any Data Definition Language (DDL) command in a database.
    • db_datareader -> can read all the data from all user tables
    • db_datawriter -> can add, delete, or change data from all user tables
    • db_backupoperator -> can backup the database
    • db_securityadmin -> modify role membership and manage permissions
    • db_accessadmin -> can add or remove access to the database for logins
    • Can View Any Definition

No Database Creation or User Creation Everything Else Automated Permission Recommendation

If granting that level of access is not workable or allowed we would recommend the following. It requires SQL Users to be manually created and the database to already exist. The process can add existing users to databases as well as deploy everything.

  • Database Permissions
    • db_ddladmin -> can run any Data Definition Language (DDL) command in a database.
    • db_datareader -> can read all the data from all user tables
    • db_datawriter -> can add, delete, or change data from all user tables
    • db_backupoperator -> can backup the database
    • db_securityadmin -> modify role membership and manage permissions
    • db_accessadmin -> can add or remove access to the database for logins
    • Can View Any Definition

Manual User Creation Both Server and Database Permission Recommendation

Here is the most restrictive permissions for automating database deployments. No new database users can be created. No new schemas can be created. Users cannot be added to roles. Table and stored procedure changes can be made.

  • Database Permissions
    • db_ddladmin -> can run any Data Definition Language (DDL) command in a database.
    • db_datareader -> can read all the data from all user tables
    • db_datawriter -> can add, delete, or change data from all user tables
    • db_backupoperator -> can backup the database
    • Can View Any Definition

Third party tools

Redgate SQL Release

SQL Release has been replaced by DLM Automation
SQL Release has been replaced by DLM Automation, which is available in the SQL Toolbelt. For more details, see the documentation for DLM Automation

Redgate DLM Automation

The Redgate Database Lifecycle Management (DLM) Automation tool works with build servers and release management tools to provide continuous integration and automated deployment for your SQL databases. You can control DLM Automation by using the DLM Automation PowerShell cmdlets or by using one of the DLM Automation add-ons. DLM Automation is available in the SQL Toolbelt.

Redgate ReadyRoll

Redgate ReadyRoll follows the change script approach, and also provides an excellent Visual Studio experience. It has the ability to generate Octopus Deploy-ready NuGet packages and can work in continuous integration/automated build tools.

Learn more about ReadyRoll
You can learn more about Redgate ReadyRoll's Octopus Deploy support in their documentation.

DbUp

The open source tool DbUp, built by members of the Octopus Deploy team and other contributors, is another alternative. This 8 minute video (with captions) demonstrates how DbUp and Octopus Deploy can be used together to perform database deployments:

Visual Studio Database Projects

Raffaele Garofalo has a blog post detailing how to deploy Visual Studio Database Projects with Octopus.

Entity Framework Migrations

Kevin Kuszyk has a blog post about deploying SQL databases using Entity Framework migrations and Octopus Deploy.

RoundhousE

Ben Tillman has a walkthrough on how to deploy database migrations with RoundhousE.