Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
Redgate Database Devops
The Voice of the DBA
 

MTTD

There are a lot of Mean-Time-To-xxxx acronyms. Many of us have heard of the mean time between failures (MTBF) for disk drives. Some of us use that information when considering which model to buy. In the DevOps world, there are also the mean time to failure (MTTF) and mean time to resolve/repair (MTTR). There is one more that I think is very interesting, and that is the MTTD: the mean time to detect an issue. This is the average amount of time it takes you to detect there is a problem after the problem occurs.

There was an outage at Monzo recently due to a database upgrade, which was recounted on their blog. In this case, their MTTD, or rather actual time to detect, was a minute. I think that is amazing. In fact, I'm somewhat skeptical that an alert is raised, someone looks at it, the customer service desk calls the Ops team (who were upgrading servers), and the Ops person realizes in the space of a minute or two that there is an issue. It's possible, but I have found that help desk personnel that discover something can take a few minutes to verify the issue and then scramble to find the on-call phone number. Relaying information can take a minute or two, so if this is accurate, huge props to the IT staff at Monzo.

Many of us strive to high a high availability number for our systems, especially databases. This is one of the drivers for the growing use of availability groups in SQL Server systems: to ensure the database is highly available to clients. In determining availability, we often speak of the percentage of time that a system is available. The holy grail is five 9s, or an uptime of 99.999% of the year. This gives you just over 5 minutes of downtime a year.

In the case of the Monzo outage, which took place in July 2019, the alert is reported at 13:14 and the incident was declared at 13:15pm, one minute later. The time to diagnose the issue (maybe another MTTxx item) was 63 minutes, just over an hour. At this point, availability is arguably down to 99.988%. The actual fix was completed at 113 minutes, or 99.978%. That's the number if nothing else happens this year.

If you're attempting to get to 5 9s of reliability, you get less than 6 minutes of downtime a year. Can you figure out what's wrong in 6 minutes? Much less fix it? That's a difficult task. I think 4 9s, giving you 52-ish minutes of downtime, is realistic, but very hard. Most of us can likely handle 3 9s, which allows for 8:30:00 of downtime a year. While I've exceeded that before, it's been rare.

We have a lot of HA (high availability) options in SQL Server, and there are many successful implementations that achieve high levels of availability for the database. The network and the application are another story, but I think the quality of those areas has increased over the years as well. Doing HA well is hard, and if you aren't 100% sure of what you're doing, or your system is very valuable, you might engage a consultant, like Allan Hirt, to ensure that you've configured things well. SQL Server runs well in HA configurations, but getting it set up can be more difficult than you expect.

Steve Jones - SSC Editor

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

Redgate Database Devops
 
  Featured Contents
Stairway to DAX and Power BI

Stairway to DAX and Power BI - Level 9: Function / Iterator Function Pairs: The DAX MAX() and MAXX() Functions

Bill Pearson from SQLServerCentral.com

As a part of his "Function / Iterator Pairs" mini-series, Business Intelligence architect, Analysis Services Maestro, SQL Server MVP, and author Bill Pearson introduces the DAX MAX() and MAXX() functions, discussing similarities and differences. He then provides some hands-on exposure to the use of each, particularly in combination with other DAX functions, in generating "largest numeric values" to meet differing needs within our PowerPivot model designs.

How to Stop a SSIS Package Execution from Code

Additional Articles from MSSQLTips.com

In this tip we look at a way to stop the execution of a SSIS data flow task that is running by executing an outside process to stop the data flow immediately.

Self-service and Delegation with SQL Clone 4 Teams

Additional Articles from Redgate

SQL Clone 4 introduces a new access control feature called Teams, allowing granular control over the SQL Server instances, images and clones to which each group of users has access. Here James Murtagh explains how Teams makes it easier to manage the safe distribution of database copies throughout the organization, to the various teams that need them for development, testing, training or analysis.

Free eBook: Troubleshooting SQL Server: A Guide for the Accidental DBA

Press Release from Redgate

Three SQL Server MVPs (Jonathan Kehayias, Ted Krueger and Gail Shaw) provide fascinating insight into the most common SQL Server problems, why they occur, and how they can be diagnosed using tools such as Performance Monitor, Dynamic Management Views and server-side tracing. The focus is on practical solutions for removing root causes of these problems, rather than "papering over the cracks".

From the SQL Server Central Blogs - Restoring an Expired Certificate–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. One common task that you might need to...

From the SQL Server Central Blogs - Are My SQL Server Indexes Being Used?

SQLEspresso from SQLEspresso

Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as...

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Formatting the date

If I have this code:
>>> import time
>>> myString = '02/08/2019'
>>> myDatetime = time.strptime(myString,"format_string")
What is the correct way to enter the format_string for a date in August?

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)

Replication Basics

Which database keeps track of the changes made to data and ensures they are replicated to other databases?

Answer: distribution database

Explanation: The distribution database is responsible for tracking the changes made in publishers and ensuring they are sent to each subscriber. Ref:

 

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 - Development
using different databases / schema with stored procedure - We set up a server with different databases. One for more financial data (ASAP_be), one for hr data (PRP_be), one for structural data (SARA_be). When I give users the right only to execute stored procedures (with no access rights directly to the tables themselves), this works well, when the stored procedure only uses tables from […]
SQL Server 2016 - Administration
Lock pages in memory setting - Hi Experts, I have a doubt on lock pages in memory. We have sql 2012/2016 64-bit enviroments. Question is, do we need explicity enable lock pages for 64bit sql servers or it is not required? As per my knowledge it is used for 32-bit systems as virtual address space is low. Thanks, Sam
3.5 TB SQL Migration from 2012 to 2016 - Hello! What is the efficient method to migration 3.5TB SQL database from 2012 to 2016? Backup and Restore is not our choice as it would take application down for long time. Will setting up transaction replication work? Or have server team copy the Data and log files from current server to new server would be […]
Administration - SQL Server 2014
how to find network issues for sql prod server ? - Hi Experts, Is there a dirty trick or easy way to check network issues or slow network for the database server without using any 3rd party monitoring tool? Can we use continous ping from some quite non-prod server to prod sql server ? can we do that or is there a better way to do […]
Development - SQL Server 2014
Safe/correct way to reduce balance from account - i have a table: id int,customerid int,balance. i want to reduce for a "customerid" from his balance,assuming his balance-@amount to reduce>=0. what is the corret/safe way to do it, assuming the reduce can be called more then once at the same time from different applications?
SQL Server 2012 - T-SQL
Insert into Select with Conditions - Hello, I think my requirement is fairly straight forward, but cant seem to get the syntax right or find a solution. Im, trying to do a Insert into Select , that will allow duplicates only if a field value in a row is null. hope that makes sense. - thanks Greg
SQL Server 2008 - General
Unsure how to define this query - I need to make a query that will select all items only with ALL their warehouses datelastsale earlier than 2016-09-01. If Wh1, Wh2 are before 2016-09-01 or blank, but Wh3 is 2017-09-01 I don't want this item to show. I first thought of using this  but I just can't put my finger on making int […]
Unsure how to define this query - I need to make a query that will select all items only with ALL their warehouses datelastsale earlier than 2016-09-01. If Wh1, Wh2 are before 2016-09-01 or blank, but Wh3 is 2017-09-01 I don't want this item to show. I first thought of using this  but I just can't put my finger on making int […]
Reporting Services
External Images - So I'm trying to get an external image to show after I deployed the report. When I run the unpublished report in Visual Studio it's not an issue. I have the Image Properties picture value to be somethings like this "File://L:/EQuipmentdata/100305/100305/thumbs/100305_tn.jpg" and it displays in design and preview. No problem. I deploy it and the […]
Row Count text box outputting above my data, i need it below - Row Count text box outputting above my data, i need it below. I have a Tablix with my report data at the top of my report. Immediately below that i have a ROW_COUNT text box. When I output the final report to Excel, the row_count is below the tablix as expected. When the client (and […]
SSRS 2016
Subscriptions - writing report Windows File Share - Is it possible to write a report to a Windows File Share without using an  account/password? thank you, beth
Subscriptions - what causes them to go into Pending state? - We often have subscriptions that go immediately into a Pending state after being triggered by a schedule. For example, I have a subscription that was executed at 12:52PM, and at 1:44PM, it is still Pending. thank you, beth
Subscription - how to pass list of values? - I have a multi-value parameter on a report, and I cannot figure out how to pass multiple values in a subscription parameter, i.e., I pass this value in parameter partner_list; 57590,12100 which produces this error; library!WindowsService_105!1f64!09/10/2019-12:36:40:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'partner_list' is not a […]
SSDT
Connect to AWS - Hi All, I am completely new to AWS and i'm having trouble getting SSIS to connect to the ODBC driver. I have set up the "Amazon Redshift (x64) driver in the ODBC "System DSN"  window and that connects fine. I have connected SSMS to the driver via a linked server using the provider "Microsoft OLE […]
Integration Services
Possible double hop issue - SSIS Package / SSIS Catalogue - Wanted to check if anyone else was able to resolve this and how they did it. In a nutshell we have a package stored in SSIS catalogue on Server – S1 which reads files from Server – S2 ( NAS share) and then the  package stored in SSIS catalogue is executed via a C# app […]
 

 

RSS FeedTwitter

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.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -