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

An Alert Philosophy

Many of you reading this will be responsible in some way for managing a system. This might be a test/development system or a production one, but often you want to know how well the system is working. Or maybe if the system is just working at all. Even developers care if their server is up.

There are plenty of ways to get information about a server. Some of us monitor in an automated fashion, some of us check when we think something is wrong, but no matter what you do, you are looking for some data about the state of the system. This is what we call an alert, though getting the alert because you can't connect and realize the system is down might not be the best type of alert.

I've managed lots of production systems, and usually have implemented some sort of process to let me know when things happen. These could be good or bad things, or just things, but they are alerts that I can about. They provide me with information that I will use in some manner to make a decision. In other words, some sort of human action is needed here.

A alert should be something that calls for human action, or at least, that's what Google thinks. This short piece contains information Google's SRE work. Their definition of an alert is something a system (not a user) generates and something that requires human action, not automated responses. The article talks about good alerts and hierarchies of alerts, and more. Everyone has their own method of picking and configuring alerts, but you should think about what interruptions you need, and what sort of timeliness is required from a human.

Personally, I have try to decide if an alert requires immediate action and if so, it needs to hit my device (phone, pager, fax machine, wife's mobile, whatever). That way I can make a decision to actually deal with the situation or pawn it off on Kendra or Grant. Those are real time decisions and there ought to be few of those in any system.

If it's not something I need to fix now, then it can be filed in email or as a lower priority item in my monitoring software. Those items need to be alerts and not logs because I only look at logs when I'm really confused and can't fix something. The low level alerts are things like I'm running low on disk space for a system and will run out in 30 days. That's not something I break away from a date with my wife for, but it is something I want to start thinking about if there's a lead time to make changes.

No matter how you view alerts, it does pay to think about them and try to reduce the number and frequency of alerts that hit your administrators. That might be configuring your monitoring differently, it might mean adding resources, or it might mean fixing broken software. We can burn out people as well as customers with constant breakage, so fix those things that are worth alerts.

Steve Jones - SSC Editor

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

 
 Featured Contents
Stairway to SQL Server Virtulization

Stairway to SQL Server Virtualization Level 3 - Building the Ideal VMware-based SQL Server Virtual Machine

David Klee from SQLServerCentral.com

In this level of the Stairway to SQL Server Virtualization David looks at the ideal virtual machine setup for a SQL Server instance on VMware.

SandDance in Azure Data Studio

Additional Articles from MSSQLTips.com

In this tip we look at how to use SandDance a powerful data visualization tool that is an extension to Azure Data Studio.

Free eBook: SQL Server Internals: In-Memory OLTP

Press Release from Redgate

In this free eBook, Kalen Delaney explains how Microsoft's 2016 In-memory OLTP engine works. In her book, learn how to use lock- and latch-free data structures to allow non-blocking data processing, and find out how to migrate existing tables to Hekaton.

From the SQL Server Central Blogs - Powershell script to find new servers in an AD domain

SQLPals from Mission: SQL Homeostasis

This is actually part of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there...

From the SQL Server Central Blogs - How do I force a SQL Login to change it’s password on next login

Kenneth.Fisher from SQLStudies

Forcing someone to change their password on a periodic basis is a pretty common thing, and any time I’ve gotten ... Continue reading

 

 Question of the Day

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

 

Basic Functions

I run this code in my R session:
myfunc <- function(x, n) {
        x^3
}
At the R prompt, I now run this:
i <- 5
myfunc(i)
What is returned?

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

 

Redgate SQL Provision
 

 

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

Accessing Always Encrypted Data

I have a column that is encrypted with Always Encrypted on SQL Server 2017. I want to query the table in SSMS, but I only can see encrypted data. I should be authorized to see the decrypted data, but I see this in SSMS v17.x. I run this code:

SELECT CustomerID
     , CustomerName
     , CustomerEmail
     , SecureCreditLimit
     , Active FROM customers

I get these results:

CustomerID  CustomerName    CustomerEmail SecureCreditLimit Active
----------- --------------- ------------- ----------------- -------
1           Steve           0x01AB3C223A6 0x018E0FB43ED     1
2           Andy            0x01B1B7A29F3 0xE060FAB9FED     1 
3           Kyle            0x01309196F71 0x01DCF7F90041E9  0

What should I do in order to see the decrypted data?

Answer: Reconnect to the database with SSMS and ensure the "Column Encryption Setting=Enabled" parameter is entered in the options.

Explanation: The setting "Column Encryption Setting=Enabled" must be added to the connection string for either a .NET application or SSMS to  view decrypted data. Your client must also have the ability to access the CMK. Ref: Configure Always Encrypted using SQL Server Management Studio - https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-using-sql-server-management-studio?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 2016 - Administration
Notification of zero records in a table - There is a JAVA app that clears and reloads a staging table, but occasionally it fails to reload.  So I am trying to figure out a way that will notify me that the table is empty for more than 5 seconds.  Thinking of a trigger but I don't think that can be used in this […]
Always on AG and SQL Upgrade - Looking to upgrade from SQL Server 2014 to SQL 2016 which has always on AG on SQL 2014. Is there a better approach to do this upgrade with Always on AG. Thinking to upgrade first from SQL 2014 to SQL 2016 and then reconfigure always on AG on SQL 2016. If it's just service pack […]
Maintenance plan was not deleting the files - I have two instances of SQL servers running from a machine. SqlServer ( default instance) is version 2012 and sqlserver\Instance1 is version 2016  . Databases from both the instances are backed up to the same drive W:\Backups. I created a maintenance plan from Sqlserver ( default instance)  to delete any bak files in the share […]
Service Account for SSIS package run from Agent Job on AlwaysOn - I have a sql sql server with SSIS. An agent job executes an ssis pacakge that copies files too and from directories. I am now converting this to alwaysOn, so planning for permissions.   When the ssis package is run via agent job, which accounts require permissions to read and write to the file directories? […]
SQL Server 2016 - Development and T-SQL
#Temporary table in query not working - I have a query that is not working and could use some help.  It doesn't exclude the software littles from the temporary table.  The create #tempTable works and I can select the titles from it, but the titles still appear when I run the query below.   CREATE TABLE #TempTable (Software NVARCHAR(MAX)); BULK INSERT #TempTable […]
Development - SQL Server 2014
Transaction was deadlocked on lock | communication buffer resources with another - I have a table that contains case numbers. The following stored procedure assigns a case from this table to the user who calls this stored procedure from a web application. There are more than 150 users who are using the web application at the same time. This means that at any moment some or the […]
Are these good and appropriate data types? - Hi all, I inherited a table that I am thinking to change some columns data types. Table has 100+ mln records and 96 columns and serves as a fact table in our data warehouse. Key column with numeric(15,0) type. All values are actually integers. Would replacing it with int/bigint be better? as_of_date char(8) - values […]
SQL 2012 - General
What causes a query to suddenly start performing poorly - SQL Server 2012 SP3 CU9 Standard 64 bit running on Win Server 2008R2 (Hypervisor) This morning the server that hosts an application database was up to 100% cpu. This application is used the same every morning for the last decade. Initially what i saw was many sessions sitting with waits and all running the same […]
Issues connecting to SQL Server using Windows Authentication - Hello,   I am trying to connect a (Win7) client my the sql server instance. SQL server 2012 (11.0.2100) was installed on a virtual server running Windows server 2012 r2.  Mixed mode was chosen during installation and also added current user (windows server user).   In the sql server properties, server authentication is set to SQL […]
Order By in an SQL query with aggregate function and CASE.. WHEN...END - I have this query which works OK. SELECT D.MStockCode AS StockCode, CASE WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) < [W_SE-MFG].dbo.[YearWeek](GETDATE()) THEN 'PAST' WHEN [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) > [W_SE-MFG].dbo.[YearWeek](DATEADD(month, 7, GETDATE())) THEN 'LATER' ELSE [W_SE-MFG].dbo.[YearWeek](D.MLatestDueDate) END AS DueDate, SUM(D.MOrderQty - D.MReceivedQty) AS QtyOuts FROM dbo.PorMasterHdr AS H INNER JOIN dbo.PorMasterDetail AS D ON H.PurchaseOrder = D.PurchaseOrder WHERE (D.MOrderQty - D.MReceivedQty > 0) […]
SQL Server 2012 - T-SQL
Best practices for comparing ToDo tasks to completed tasks when the ToDo tasks c - Best practices for comparing todo tasks to completed tasks when the todo tasks changes over time   I have a situation where I am required to compare a list of todo items to a list of done items and return 'Completed' when all of the todo items are complete. When there are pending todo items, […]
SQL Server 2008 Administration
Migration from server to server - I recently migrated from Windows 2003 SBS running SQL 2005 Express along with SSMS 2005 Express. The new server is Windows 2008 R2 SP1 and has SQL 2008 R2 Express and SSMS 2008 Express When I restore the Database it appeared to populate in the LEFT Pane of SSMS but when clicking on an object […]
SQL Azure - Development
Building SSIS Projects In Azure DevOps - My usual way of doing this on-prem is to call Devenv.com, with appropriate parameters, but that option does not seem to be available in Azure DevOps. How are other people doing this? I've seen that there is an SSIS Build Marketplace add-in, but it's not licensed for commercial use & therefore doesn't help me here. […]
General
MSDN Subscriptions - 5 years ago I use to have an annual MSDN Subscription which enabled me to download versions of Win OS's SQL Server Visual Studio As a freelance developer the subscription service was great learning and development testing different versions of MS products. Looking now I see that MSDN does not exist anymore. Wanted to see […]
Integration Services
Immediately Fail Package Uppon Failure - Hi, Very simple question here but can't seem to find the answer... I have a SSIS package that does nothing but call other SSIS packages, all in parallel (no precedence contraint) I want the parent package to fail whenever any child package fails... That's the easy part... But I wan't the parent package to fail […]
 

 

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

 

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