{"id":1899,"date":"2014-11-05T00:00:00","date_gmt":"2014-11-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/database-configuration-management-for-sql-server\/"},"modified":"2021-08-24T13:39:41","modified_gmt":"2021-08-24T13:39:41","slug":"database-configuration-management-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/database-configuration-management-for-sql-server\/","title":{"rendered":"Database Configuration Management for SQL Server"},"content":{"rendered":"
\n <\/p>\n

DevOps, Continuous Delivery & Database Lifecycle Management<\/strong>
\n Automated Deployment<\/p>\n<\/div>\n\n

This article aims to describe the major scripts, documents and lists that are needed to ensure that changes to a database can be easily tested, deployed, and maintained in service. <\/p>\n

All RDBMSs can, like an application, be scripted. Of course the TSQL scripts for the DDL of the SQL Server databases need to be in source control, in an VCS (Version control System) repository , but for database configuration-management, this is where the story begins. <\/p>\n

An RDBMS is different in many subtle ways from an application, and has rather different requirements. If the aim is to implement a ‘Continuous Integration’ (CI) process that provides a scripted deployment, it is dangerous to assume that the practices of application configuration management will work, without elaboration or modification, for the many procedures and processes that make up database lifecycle management. <\/p>\n

From the developer’s perspective, a database that is ready for delivery may be seen as a collection of ‘logical’ database objects, such as tables and routines, columns, keys, and constraints. Other processes such as ETL, alerting, and messaging may only be represented by a stub or are ‘mocked’ sufficiently for a successful build and test. The access-control model may, at this stage, be rudimentary. Processes such as replication and log shipping will not be represented at all. The analysis of data for business analysis and reporting via SSAS is not likely to be considered at this stage. The only data that is required consists of the ‘static’ data that is required for the database to run. <\/p>\n

For the Database developer and administrator, there lies ahead a great deal to attend to beyond this simple model for a database of any realistic size and complexity. All the systems that are required, or are already in place, beyond the ‘logical schema’ that has been defined in development must be checked to make sure there are no repercussions of the change. This article aims to explain some of the scripts and documents that are necessary merely to automate the bulk of a database deployment and subsequent management; and which need to be held in a repository. <\/p>\n

The more frequently the entire delivery process is done, the easier it gets, because everyone gets alerted more quickly of potential problems. Even the process of changing database objects, regardless of their type, will not always be a trivial operation. Changes often impact dependent objects and sometimes even underlying data. The process of analysing and accommodating these dependencies is traditionally time-consuming and prone to error. By deploying frequently as far as staging, many of these issues are identified, scripts tested, and the process automated where possible, long before the decision is taken to deploy to production.<\/p>\n

Keeping track of changes<\/h1>\n

To prevent problems, or to react quickly when they happen, it helps greatly to know what has changed with the system. Different specialists can spot the repercussions of any change from their own viewpoint. With a corporate-scale database that is essential for the revenue-generation of the organisation, it is essential. It is also likely to be a legal requirement. ‘Version creep’, or ‘version drift’ has to be monitored and dealt with as well as changes that are the result of the legitimate development process.<\/p>\n

Custodian of data<\/h2>\n

Administrators need to continuously monitor changes to production databases to detect unauthorized changes. This is a broad remit, but obliges the DBA to ensure that all changes, even if as innocuous as an index being dropped, are accompanied with whatever change approvals are required, so that no valuable changes get lost. <\/p>\n

Sadly, not all unauthorized changes are innocuous. There is, in consequence, a legal requirement for databases used by commerce or holding personal data to comply, in terms of auditing and security, with national and international standards. This is another important aspect of life cycle management. It is not only to ensure database efficiency, maintenance, and ease of operation, but to safeguard the interests of customers, users, and shareholders.<\/p>\n

Different requirements for different installations<\/h2>\n

At various stages in the development cycle, there will be several copies of an application’s database that have to be maintained for various purposes such as development, staging, production, and the several types of testing. Each setting has its own requirements. <\/p>\n

Depending on the purpose of the installation and the nature of the data, each copy of the database is likely to have different logins, roles and permissions. They will also have different configuration, physical file locations, replication, Service Broker, ETL systems, instrumentation and scheduled agent jobs. There will also be differences in alerting systems and even possibly in the way that errors are managed.<\/p>\n

What constitutes the core database? Not access-control, surely, since a database used for testing is unlikely to share the same access-control as the database in staging or the production system. We can’t script out the replication artefacts since they would cause replication to break in any database with these artefacts in place. Scheduled jobs are rarely scripted out, yet these can easily contain scripts without which the database simply wouldn’t function. <\/p>\n

Where changes take place<\/h2>\n

Production databases are not immune from schema change, even if the change is merely an adjustment to indexing strategy. In a normal business setting, all changes to production databases must adhere to proper production control procedures. <\/p>\n

In-House development Applications<\/h3>\n
\n

To ensure that nothing gets lost, and that the release processes are done using the correct versions of the code, the versions of the database that are deployed and maintained for development purposes must match the versions in source control. If, for example, the version undergoing integration tests has been altered without these changes being reflected in source control, and transferred to trunk, then the results of the tests become meaningless.<\/p>\n

When a system is being actively developed , development databases are used. Depending on the methods used, these might be a multitude of local machines where changes are aggregated in source control. It could be one or more shared development databases. Whatever the model, developers make changes that the database administrator must then consolidate and propagate to staging or test databases. There are a number of tasks that are necessary. <\/p>\n

The changes made to development will be reflected by the version in Source Control. The database must be built from this TSQL code to ensure that it compiles and runs. Because so many additional services and components may be required, such as CLR components, agent jobs, and server-based COM objects, the build will check that these are ‘scripted’ in the build script. As part of the database scripts, there will probably be simple ‘assertion’ tests provided, at component level, by the database developers that are designed to check that each component does what is expected, and that dependent objects are what is expected. If all is well, then these changes are made to staging or test databases. <\/p>\n<\/div>\n

Third-party applications<\/h3>\n
\n

Typically, most applications will get upgraded over time by their provider, usually a third-party. In the meantime, many third-party database applications are customized by the business user as required to support the application. This can be for a number of good reasons, such as replication, performance, auditing, or instrumentation \/ monitoring. Application customizations are usually dependent on upgrade scripts supplied by the application vendor, or in some cases is done via a remote login by the vendor. In most cases, the production team can only guess at the impact of the upgrade procedure on their customizations. Without finding out, all manner of processes could inexplicably break. When customers wish to determine the effects of an upgrade, they compare the database schema before and after the upgrade to find out what’s changed. This tells them how their customizations will be impacted as a result of the database being upgraded.<\/p>\n<\/div>\n

Managing Database Change<\/h2>\n

The administrator who is managing a deployment will aim to automate as much as possible of the process. In a simple system where a deployment can fail without a risk to security or business, that could include almost all the process, but with any corporate system there are likely to be tasks that require the DBAs expertise, and will require manual intervention as well as signoff. At one end of the spectrum of risk, a synchronisation script can, in a trivial deployment, be allowed to be automatically generated and immediately applied to the destination. At a certain point in the spectrum, a manual check of the script by a DBA, and signoff, is required. For a typical deployment, The Database Administrator will be involved in three major tasks.<\/p>\n

Change specification<\/h3>\n
\n

The changes to the logical database model in source control represent a specification of the required changes to the production system. These may be made for a variety of reasons including business mergers, legislative changes, new business requirements, and application changes. There will be an impact on other systems, but these are unlikely to be recorded in the database source control. There are likely to be repercussions: The hardware must be checked to ensure that it can support the changes. The software configuration will need a check-over. A change to the database model will, for example, affect onward analysis and ETL. In some circumstances, this will require changes to the logical model before deployment can take place. To ensure rapid delivery, the DBA works ‘upstream’ with the development team to ensure that this never happens, and the change specification document, in whatever form it takes, becomes a way of ensuring that there are no surprises on either side. At the specification stage all changes are planned, reported and costed. In some organisations, this will require signoff.<\/p>\n<\/div>\n

Deployment<\/h3>\n
\n

The deployment process is that of changing the database to reflect the conditions that were specified in the change specification phase. Deployment includes changes to other systems and services, from the obvious such as hot standbys to the subtle, such as analysis and reporting services, alerting, instrumentation, monitoring. Deployment does not only encompass the process of applying the changes to the actual database, and if necessary, undoing the changes. In some cases, it will involve roll-forward<\/a>, with the subsequent changes being reflects in source control<\/p>\n<\/div>\n

Managing the source and configuration<\/h1>\n

As well as the source build scripts kept in source control, the change-management process requires other scripts and records that allow developers and database administrators to manage a variety of changes in database environments: These should be in a SCM repository as well.<\/p>\n

These scripts should be used for all installs so that every participant in the development process effectively ran a product upgrade each time they set up tests or development instances. This results in scripts that are well tested long before they hit production. <\/span><\/p>\n

After a change is made to a production system, the changes to the database model must be itemised to help any audit to the evolution of the database model. Auditing documents who performed what actions, and why. This allows anyone to detect and, if necessary, prevent any ‘uncontrolled’, unknown or unanticipated access to the data.<\/p>\n

These are a variety of scripts and lists that help to manage databases during delivery..<\/p>\n