SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Switching the time zone

The Voice of the DBA

The Change Failure Rate

One of the measurements used in DevOps to determine if your team is improving is the change failure rate. This is the number of times that there is a problem with a deployment as a ratio of the total number of deployments. The idea here is to determine the reliability, and to some extent, the risk of deploying changes in your environment.

In the past, I've been very successful with deployments as a DBA or developer. Often I've completed database changes within the change window, with success that allowed our applications to run after changes. That doesn't mean I pressed a button and the deployment worked. On a regular basis, my expertise with SQL was needed to fix a script or re-run a process, or make some other "development change" in the production environment to ensure the entire deployment completed. While still a minority of times, this wasn't an uncommon experience.

Many people have had the same experience, as the State of DevOps report has shown for years. In both the application and database worlds, doing anything other than pressing a button or running a single script is a deployment failure if the steps aren't clearly documented and run to completion without any alteration. Not a catastrophic failure, and certainly one that many of us can recover from, but still a failure from the standpoint of being ready to deploy and having a reliable process.

Quite a few of us have made a career of cleaning up other people's messes in deployments, with our ability to get changes deployed and applications back up and running being a testament to our expertise and skill. That's not a reliable process, especially when an organization is forced to depend on a Steve or a Brent or a Kendra to ensure smooth deployments. That's a recipe for disaster, especially as all of us want to go on holiday, undisturbed by some software "emergency".

I think this is one of the more telling metrics for a strong software development process and a reliable deployment process. If this number isn't extremely high, in the 95-99% range, then our organizations are spending resources, especially time, on items that don't add value. Instead, we ought to invest in moving to a DevOps style process that allows our expertise to be used solving new problems, not cleaning up the mess of the poor development practices of others.

Steve Jones - SSC Editor

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

Featured Contents

Create and deploy Azure Resource Manager templates by using the Azure portal

Deepak Sahu from SQLServerCentral

This article will explain and help to create and deploy ARM templates using the Azure portal. When Azure was first created, Azure resources were managed using the Azure service management (ASM), which is also known as “Classic”. Now this has been replaced and is called the Azure resource manager (ARM) API. ARM is the deployment […]

Date and Time Conversions Using SQL Server

Additional Articles from MSSQLTips.com

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it.

SQL in the City Summits Down Under

Press Release from Redgate

Redgate are inviting senior data professionals to attend one of the upcoming SQL in the City Summit events taking place in June. If you’re interested in learning how your business can benefit from implementing Compliant Database DevOps this event is for you. Find out who’s presenting and register for a Summit near you today.

From the SQL Server Central Blogs - Using Extended Events To Capture Backup and Restore Progress

SQLPals from SQLServerCentral

If you are running a DATABASE BACKUP or RESTORE command manually, SQL Server will show you the progress at a specified % completion interval. For the BACKUP, the default is...

From the SQL Server Central Blogs - Turning a Corporate Color Palette into a Data Visualization Color Palette

Meagan Longoria from SQLServerCentral

Last week, I had a conversation on twitter about dealing with corporate color palettes that don’t work well for data visualization. Usually, this happens because corporate palettes are designed...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Switching the time zone
My nephew was born in Paris at 3:02am this morning. My brother recorded the time in his system as: INSERT kids VALUES ('Ravi', '2019-06-03 03:02:00 +2:00') I got a phone call a few minutes later, and could access the data in his table. I want to convert this to my time in Colorado, which is 8 time zones away. What do I replace the XX with in this query to get the right time? SELECT XX FROM dbo.kids AS k WHERE k.kidname = 'Ravi'
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Service Broker Queues

How is a queue represented in SQL Server?

Answer: A hidden table

Explanation: A queue is implemented in SQL Server as a hidden table. Ref:

Service Broker Basics Part 1 - https://www.sqlservercentral.com/articles/service-broker-part-1-service-broker-basics
Queues - https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms166053%28v%3dsql.105%29
CREATE Queue - https://docs.microsoft.com/en-us/sql/t-sql/statements/create-queue-transact-sql?view=sql-server-2017

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 - Administration

SQL Server Instance Failure - Monitoring - Hi We've had an issue this morning (and still ongoing) that our main SQL Server has failed. However, we at the beginning there was no indication that the failure was in SQL. Eventually when digging deeper, we noticed that our two instances on our main VM for SQL, both SQL instances were in a stopped […]

prod and stage comparison - Hi all, I can see that identical request is executing much longer on stage comparing to prod. How do I check if it is CPU, memory or hard disk bound? Please advise.

SQL Server 2017 - Development

SQL lookup tables - Hello, I am very new to SQL and would really appreciate some help. I am using Microsoft SQL Server 2017 to design a database for matching product features to products. To do this I have created the following 3 tables Products Spec Lookup The lookup table joins the ID's from the product […]

SQL Server 2016 - Administration

SQL Agent. Run T-SQL in scheduled job against a DB in a different SQL instance. - Well, that's what I'd like to do! This is so I can keep the scheduling config and T-SQL on one admin SQL Server, whilst the databases reside on different servers. This'd mean I can email using SQL Mail from my admin instance too. I'm seeing a few things that might be of interest, in Linked […]

SQL Server Transactional Replication - We are uplifting one of our servers to SQL Server 2016. We are going a side by side install. The machine we are doing the uplift is the Publisher. The distribution and subscriber server are not being touched. this is a push not a pull. So I'm thinking I will have to reinitialize the snapshot. […]

Linked server error 18456 - Hello Team, Iam getting the below error while creating the Linked server with windows authentication. Msg 18456, Level 14, State 1, Line 88 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

SQL Server 2016 - Development and T-SQL

PDF stored in table as varbinary max, how to convert and display it SSRS report - Hi , I have a table where 10000+ pdf stored in table as varbinary(max) How to convert them with T SQL to display in SSRS Report as images? Thank you

Indexing VARCHAR. Does Length Matter ? - We have some TXT file imports into SQL tables. As a default, the columns are set to varchar(500) to match the incoming definition. However, the data will never be 500. Is there a penalty to indexing a VARCHAR(500) column, if the max length will be 50, compared to defining a VARCHAR(50) column and migrating the […]

SQL 2012 - General

Table type - I just want to validate my use of a custom type. I'm doing a query on that I need to pass an unknown number of string values to. I created a custom table type, with a script that looks like, What length should I make the field, for it to be of use to others? […]

How to save collapse sections of the query window and save it like that - Hi, My manager would like it that when he collapses certain parts of the query and saves it, it will open that way; and not have it all expanded. Is there a way to do this? Thank you

How to list counts hour by hour - Hi, Below I have a sample of the output, and below that is a sample of how the data is stored in the database. I am trying to figure out how the queried this to get the calls per hour, I looked online but could not find anything exactly like this maybe ai am sating […]

SQL Server 2012 - T-SQL

sql server 2012 data column used for 7 different languages - In a sql server 2012 database, I want to store some messages in the database for the following languages: Karen, Arabic, Nepali, Burmese, and Kiswahili. This would be in a column called stringtemplate that is currently defined as varchar(max). **note: this column currently stores messages in English and Spanish. Can you tell me if I […]

SQL Azure - Administration

Azure data sync - Hi, I've automated the schema change propagation to the member database using this approach: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-update-sync-schema The schema changes go through fine. I've run : alter table mytable add blah varchar(20) go update mytable set blah = 'blah' go I could see the column 'blah' on the member database, but the update did not go through. […]

SQLServerCentral.com Website Issues

What happened to marking as answer? - Hadn't noticed this before, as I don't I hadn't posted any topics that are asking a question, but I noticed that the mark as answer button has gone. Guessing that it wasn't used often and so removed, but just checking in case I was going crazy.

Job Postings

DBA vacancy at Sheet Music Direct - Milwaukee, WI - SheetMusicDirect.com, a division of Hal Leonard, is the world’s premier destination for digital sheet music. Offering musicians around the world access to instantly downloadable sheet music and all-access streaming sheet music via its popular subscription, PASS, SheetMusicDirect.com is looking for a talented and motivated SQL Server DBA to join us in revolutionizing the digital sheet […]

This email has been sent to {email}. 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.