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

Monitoring After Deployment

This editorial was originally published on Apr 17, 2015. It is being republished as Steve is on holiday.

Deployments of database and application changes have tended to be stressful in my career. I've seen the actual deployment of changes fail, installation programs crash, the startup of an application error out, or the famous blue screen of death from a .DLL change. I've been most wary, and worried, when the changes were out of my control, and I had no chance to review things.

For most database upgrades, however, I've been able to examine scripts. Even when an installation program is making changes, I could run a test on a separate instance and use Trace to capture the SQL that would be executed. However even when the actual installation succeeds, what I'm often most worried about is a logic or performance problem that won't reveal itself for hours, often not until the next business day when the system is under full load.

This week I wanted to know what formal (or informal) checks people might have to detect issues. I'd like to know if you are you actually monitoring systems in a scientific way that allows you to determine if some database deployment has gone awry.

What Monitoring process do you use to determine if a deployment is working?

One of the customers for Red Gate software noted recently that they have a series of metrics being captured on their systems with SQL Monitor. They can compare the performance of two time periods, say the 4 hours before a deployment with the 4 hours after (or 4 hours the next day) to determine if any issues have surfaced. That's similar to what I've done in the past for performance using other tools, or my own metrics. I've often kept baselines of common queries run during the day, and I can compare that to post-deployment metrics to uncover potential issues.

Logical errors are more problematic, and I've often wanted to be sure I had a couple of backups stored, and at least one backup on a warm standby in case we find information is being incorrectly changed or calculated. Those are the worst errors, and since they is no way to determine how they might surface, I have usually ensured myself and other DBA staff were on standby to perform some manual ETL to move data around between databases in a hurry.

Monitoring is an important part of being a DBA. It's not as critical as backups or security, but it's not far behind in letting you know when something is not right. Let us know this week what tricks you might have to ease the worries you have after database changes.

Steve Jones - SSC Editor

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

 
Redgate SQL Provision
 Featured Contents

A Plunge Into TOP

Alessandro Mortola from SQLServerCentral

Introduction TOP is one of the many syntactical operators available in T-SQL and at a first view, it could seem very simple and not particularly interesting. According to the official documentation, it “limits the rows returned in a query result set to a specified number of rows or percentage of rows”. The following is the […]

SQL Clone for Unit Testing Databases

Site Owners from Redgate

Phil Factor demonstrates how to use SQL Clone to create 'disposable' SQL Server databases, for development and testing work. You can spin up a clone, use it to unit test your code, messing up the clone in the process, then reset the clone in seconds, ready for the next test.

SQL Server Common Table Expression vs Temp Table

Additional Articles from MSSQLTips.com

In this tip we look at how to use Common Table Expressions CTE in SQL Server including the syntax, use cases, using more than 1 CTE at the same time and recursive CTEs with a parameter.

From the SQL Server Central Blogs - Predicate Execution Order on Mixed Data Type Columns

Bert Wagner from SQLServerCentral

Watch this week’s episode on YouTube. SQL Server’s cost-based query optimizer does a pretty good job of figuring out what order to filter your data to get fast query...

From the SQL Server Central Blogs - “Ups…I have deleted some data. Can you put it back?” – dbatools for the rescue

Cláudio Silva from SQLServerCentral

Few days ago I received a request to restore a dozen of tables because someone have deleted more data than it was supposed. I immediately thought about dbatools for...

 

 Question of the Day

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

 

Limiting Access

I have created a role for my junior DBAs with this code: 
CREATE ROLE JuniorDBA
ALTER ROLE JuniorDBA ADD MEMBER SallyDev 
ALTER ROLE JuniorDBA ADD MEMBER JoeDBA
ALTER ROLE JuniorDBA ADD MEMBER SarahDBA
-- ...
I have granted a number of rights to this role: 
GRANT SELECT ON dbo.UserAccessRoles TO JuniorDBA
GRANT SELECT ON dbo.ETLJobHistory TO JuniorDBA
GRANT SELECT ON dbo.DBALogger TO JuniorDBA
-- ...
If I run this, what happens? 
REVOKE SELECT ON dbo.DatabaseSizeInfo FROM SallyDev

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)

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'

Answer: SWITCHOFFSET(k.kiddob, '-06:00')

Explanation: The answer is SWITCHOFFSET(k.kiddob, '-06:00'). I am six time zones from UTC, while Paris is 2 time zones ahead. This means I am switching the time zone offset from Paris (+2) to Colorado (-6) 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 - Administration
What is going on with my rebuilding index job? - We migrated a major server to a new cluster.  Installed SQL Server 2017 and set up all of the nodes and the AGs. After a couple of months, we started seeing an occasional blocking of jobs in the morning.  The indexoptimize_user_databases job was still running. We put in some code to kill the job at […]
Transaction Log (Waitype=Replication) - Hello all, I have an issue with my growing transaction log. When I query the log_reuse_wait_desc we found that it is pending for "replication". I have both transactional replication and CDC enabled at the same time. It means that it is actually sharing the same log reader agent. We are not sure which exactly the […]
SQL Server 2017 - Development
Dynamic query based of business rules - I hope you are well. I need your help and advice on a repetitive task that I would like to automate. Every month my manager asks me to run a series of SQL queries based on the same table and columns. I thought I could create a rules table that would allow him to create […]
TSQL query not working properly against Full Text Index - Hi, I've been having this issue for quite some time and need some help to understand it. I am not sure if this is an "expected behavior" or, as the user is indicating, an issue that needs to be fixed or corrected somehow from MSSQL. This query suppose to return two rows SELECT * FROM […]
SQL Server 2016 - Administration
memory leaks sql server 2016 Ent - Good day) I'm sorry for my English server 2016 Ent I have a problem with memory leaks SELECT sqlserver_start_time, physical_memory_kb/1024/1024 physical_memory_GB, committed_kb/1024/1024 committed_GB, committed_target_kb/1024/1024 committed_target_GB, visible_target_kb/1024/1024 visible_target_GB -- stack_size_in_bytes/1024/1024/1024 stack_size_GB FROM sys.dm_os_sys_info(nolock) physical_memory_GB =1023 committed_GB = 874 I try to freeproccache, buffercache, but this dont't help, only restart service help solve this problem for a […]
Row Level Security - Enabled but not Filtering - ...this is a 2nd attempt to post, as I can't seem to find the 1st from the day before...   I'm trying to implement row-level security in SQL Server 2016 but am getting stuck.  Below is the code being used for the predicate function, security policy and an intermediate table.  From what I can see, […]
SQL Server 2016 - Development and T-SQL
Insert query help - I have a table with the following data ( sample) Col1  Col2  Col3   Col 4 A     AA   ABC     ALL B    BA     BBC     A C   CA     CCC        ALL   I need the result as follows Col1  Col2  Col3   […]
Query help on Pivoting on two columns - DECLARE @RegionCounts Table ( TYear char(2), Region varchar(30), RowCnt int ) Insert into @RegionCounts values ('13','Latam',100) Insert into @RegionCounts values ('13','NOAM',200) Insert into @RegionCounts values ('14','Latam',300) Insert into @RegionCounts values ('14','NOAM',400) select * from @RegionCounts   I need the out put as [13latam] , [14latam], [13NOAM], [14NOAM] as column headers with corresponding values under it.
Administration - SQL Server 2014
Replication - Stop transactional replication steps, Step 1 Stop Log Reader Agent Local publication folder and then right click a publication Click View Snapshot Agent Status or View Log Reader Agent Status Click stop Step 2 Expand SQL Server Agent folder and then expland Jobs folder Click Stop REPL-Distribution Job Let me know if I am missing any? How long […]
Development - SQL Server 2014
Query containing while loop for dates taking increditbly long - Hi All, I'm hoping to get some insight to an issue we're experiencing with a query in an SSIS package, which has been taking ages to complete. It takes about 3 hours in our Dev environment, and progressively longer as we've tried deploying to the upper environments. We thought as first, it might have to […]
SQL Server 2008 - General
retrieve all .rdl files from report server - Is there a folder on the report server that houses all of the .rdl files for all of the reports. We need to add all of them to TFS. Was hoping we dont have to go through them one at a time.
T-SQL (SS2K8)
Help Needed in sql row to column - Hello, Below is my sample data with data as ( select 1 as ID,'Apple' as ProductName,'1' as serving, 'g' as unit union all select 1 as ID,'Orange' as ProductName,'2' as serving, 'mg' as unit union all select 1 as ID,'Pinapple' as ProductName,'1' as serving, 'g' as unit union all select 2 as ID,'Apple' as ProductName,'1' […]
Reporting Services
Issue with labels in a pie chart - I have the following issue with labels in the pie chart:   In the preview of Visual Studio the issue doesn't exist but if I save in pdf then I'll find the error inside the pdf. Please, there is someone can help me? It's urgent...
Continuous Integration, Deployment, and Delivery
Response to Bloor article on SQL Provision - I found the article https://www.sqlservercentral.com/articles/bloor-scores-sql-provision-4-5-out-of-5-for-test-data-provisioning Interesting if a little shallow.  It would be more convincing if it did some A-B comparisons with major competitors (e.g. Delphix, Actifio, Denodo and Rubrik and probably others).
Third Party Products
Idera SQL Compliance Manager, not showing events?? - Trying to test this out for work, to see if it might work for some of our monitoring requirements (security-type stuff, not performance) and I've got it up and running in a lab environment.  But.  The console isn't showing any events, despite me generating events it should be reporting (failed logins, etc) I've got it […]
 

 

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

 

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