A community of more than 1,600,000 database professionals and growing

The Voice of the DBA

Always Canary

This editorial was originally published on Dec 3, 2014. It is being republished as Steve is on vacation.

Deploying software is hard. Despite decades of experience working to update software smoothly, it seems that many of us tasked with that responsibility find ourselves nervous about making changes. This is especially true for database changes, where we must not only update the schema and code, but we must preserve the data as well. If only we could drop and recreate the database during every deployment.

One of the ways in which you can make changes in a live environment is to have a second system already in place and running. With a second system available, you can stop the secondary system from handling user requests, make changes, test them, and if things are working well, you can have users connect to the updated system while you take the one one offline and perform your updates there.

This is the same process that many people use to patch clustered database servers, though the "rolling upgrades" are usually done for patches from Microsoft. It would be great if we could easily apply this same system for our schema upgrades. Deploy our changes to the passive node, have the application able to connect to this node through some switch, test the changes, and if things are working, switch users over. The problem is that our databases on the passive node aren't live.

Really the ability to deploy database changes to a live system and perform checks requires a strong architecture in both the database and application to support this. Your code must be able to handle additions to the database objects without breaking. The use of feature flags, turning functionality on and off with switches, allows certain users to test new features without most clients being aware of the changes.

It's a pattern that more and more software companies are using when they want to deploy changes rapidly to systems for limited numbers of users. However it's not a design pattern that is widely used. I hope we get more sample applications and reference architectures in the future that will help developers and DBAs code more robust and resilient applications, allowing changes to be deployed live without affecting every user.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

CI/CD for your SQL Server database

Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control.
Try it free

How to make your SQL Server development GDPR ready

Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. Find out more about how our tools help with GDPR compliance

Featured Contents

PARSE

Matthew McGiffen from SQLServerCentral.com

Introducing the PARSE function, brought to us in SQL Server 2012. More »

Placing a Mark in SQL Server's Transaction Log for Fall Back

Additional Articles from Database Journal

How many times have you had a programmer come to you and say they want you (the DBA) to restore their database to sometime prior to when they accidentally corrupted it? If you are doing FULL transaction logging you can do a point in time recovery to restore the database to just prior to when the corruption occurred. But in order to do that you need to know exactly when the programmer corrupted the data, which in a lot of cases is not known down to the second. More »

Using Dynamic SQL in Stored Procedures

Additional Articles from SQLTeam.com

Dynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. More »

From the SQLServerCentral Blogs - SQL Server data flow into Excel template with Power Query function-based parameter

Rayis Imayev from SQLServerCentral Blogs

(2018-July-15) Your case is to create an Excel template with data extracted from a backend SQL Server database; it takes a... More »

From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 111 – Horizontal Bar Chart)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Horizontal Bar Chart. The Horizontal Bar Chart is similar to... More »

Question of the Day

Today's Question (by Steve Jones):

I make a backup with this code:

 BACKUP DATABASE AuditData TO DISK = 'auditdata.bak' WITH COMPRESSION, CHECKSUM, INIT; GO

I try to restore this with this code:

 RESTORE DATABASE FROM DISK = 'auditdata.bak' WITH REPLACE;

What is the result?

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Backup and Recovery.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload.

Pick up your copy of this great book today at Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Evgeny Garaev):

What is this query doing?

 DECLARE @a INT = -1, @b INT = 4; SELECT @a = @b + @a, @b = @a - @b, @a = @a - @b; SELECT @a, @b;

Answer: Swaping values of @a and @b

Explanation:

This code is swaping values of @a and @b.

This is similar to the question: https://www.sqlservercentral.com/Forums/1973802/Assignments

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Development

Convert horizontal table to vertical in Sql - I have the following table format: need to convert this table to following format:

Update table using inner join - SELECT x.pkproductid ,x.supplierbarcodecorrect ,x.Code ,x.pksupplierid from Entertainer.dbo. x left join dbo.supplierpack spk on spk.fkproductid = x.pkProductID and spk.fkSupplierID = x.pkSupplierID The above quer

float error in t-sql scalar function - Hello Everyone, The below code is giving me error: Msg 3623, Level 16, State 1, Line 27 An invalid floating point operation occurred. [code...

Need Help to convert DATETIME result into DATE on SQL Server - How can I convert the result into a DATE datatype as '0001-01-01' isn't supported with DATETIME? I've tried DATETIME2 but...

SQL Server 2016 : SQL Server 2016 - Administration

Licence doubt - Hi, How many SQLserver(2016 enterprise edition) licences required for two( 2) Virtual machines and four(4) Core machines each to build always...

AG over different subnets and using linked servers - We have a typical AG set up, there are 2 nodes in one data on same subnet and 1 node...

dm_db_index_usage_stats not accurately reflecting index usage - Hi all I'm trying to resolve a performance issue we are having with a stored proc - i've recently added a new...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Intermittent sql_variant problem on COMPATIBILITY_LEVEL >= 120 - I have encountered a reproducible but intermittent problem when working with sql_variant values in SQL 2016. The problem does not...

SQL Server 2012 : SQL 2012 - General

SQL Server 2012 : Error while scheduling database backup with multiple backup files - Hi, Receiving attached Error while scheduling database backup with multiple backup files. Job was working fine till last week but I wanted...

SQL Syntax HELP on merging enrollment spans - If object_id('tempdb..#SPAN1') IS NOT NULL DROP TABLE #SPAN1; If object_id('tempdb..#SPAN2') IS NOT NULL DROP TABLE #SPAN2; CREATE table #SPAN1( name VARCHAR(20),...

SQL Server 2012 : SQL Server 2012 - T-SQL

Trying to drop an invalid column name without recreating the table. - Hi all, Was wondering if it was possible to drop an invalid column on a table which a developer has somehow...

delete all apart first first record of episode? - I have a table full of episodes. So for one episode there might be 10 records. I want to delete...

SQL Server 2008 : SQL Server 2008 - General

View creation: Using UNION ALL on dynamic schemas - Hi, I want to create a VIEW that combines same as well as different tables from different as well as same...

SQL Server 2008 : Security (SS2K8)

A call to ‘LogonUserW’ failed with error code : ‘1385’ - I'm pulling data from a NAS share and have recently run into the following error. An error occurred during the execution...

Programming : General

Retrieving distinct values from two tables in vb.net -

Data Warehousing : Integration Services

SSIS package execution fails when trying to load large size Excel file via SSIS Catalog (SSISDB) - Hello, I was working on a SSIS Package which simply loads an excel file into a SQL table without any transformations. The...

SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile with same datatype - Please help me asap. SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile...

SQL Server 2005 : Administering

SQL Sever Scheduled Jobs - Hi, I'm trying to set up a scheduled job that runs daily and sends out an email to me when a...

SQL Server 2005 : SQL Server 2005 Integration Services

How to write/ combine two expressions in Derived column - Hi, I have two expessions that I want to combine to be used in one column in derived column. How can...

SQL Server 2005 : T-SQL (SS2K5)

How to convert INTEGER to TIME - Hello, I have a column which is integer data type and it represents number of seconds. I need to convert it into...

This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.

This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.