The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Many of us work in situations where we feel pressure to get work done at a pace that's faster than we might like to move. In some sense, this is the nature of life, where customers and clients always want something done immediately. Whether they're ordering food or building a house, it seems that quite often the customer expects the creator to just work harder and faster, without making mistakes, and deliver the goods quickly.

That may or may not work, but if does often result in issues in software. While we can fix them, there can sometimes be larger issues, especially where sensitive data is involved. There was an incident recently that reminded me of this, though fortunately, it appears the data loss was minimal in scope and sensitivity.

The mobile app at a recent security conference leaked data. The builders of the app embedded security keys and passwords that allowed anyone that registered to download a database of attendees. Fortunately this was a limited set and it appears only names were exposed. However, it could have been much worse, especially if this were a typical non-normalized database that might contain all data about an attendee in one row.

I don't know the timeline here for development, and I certainly don't know the requirements. I do know that embedding keys and passwords into application is a bad idea, and even worse when those applications are going to be installed on customer devices. These are fundamental rules, and I certainly hope that whoever worked on this application, and anyone reading about this story, knows not to do this again.

No matter how rushed we are, it's important that we follow some practices and include some seucrity in our systems. I'd argue that data security ought to be number one and built into the system from the start. As the GDPR asks, we should be ensuring this is included by design and default. As much as it might seem that new legislation is overreaching and burdensome, I'd argue that mistakes like this one are all too common when we feel pressure to get work done. We shouldn't be making these mistakes, nor should be be pressured to ignore security for the sake of expediency.

Steve Jones from SQLServerCentral.com

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

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Virtualization and Containers

Provisioning SQL Server Instances with Docker - We DBAs tend to make a meal out of provisioning SQL Server instances. We just aren’t used to the idea of being able to get a SQL Server instance up and running without a lengthy setup and the installation of all those prerequisites. However, our Ops ......(more)

Netflix's Container Management System Is Now Open Source - Titus will offer developers the ability to scale to Netflix size proportions and seamless integration with Amazon Web Services. ...(more)

T-SQL

Concurrency Week: How to Delete Just Some Rows from a Really Big Table - Say you’ve got a table with millions or billions of rows, and you need to delete some rows. Deleting ALL of them is fast and easy – just do TRUNCATE TABLE – but things get much harder when you need to delete a small percentage of them, say 5%. It’s especially ......(more)

Concurrency Week: Can SELECTs Win Deadlocks? - Yes, especially when they’re part of a larger transaction. In this example, I’m going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency ......(more)

Calling Http endpoints in T-SQL using CURL extension - SQL Server Database Engine don’t have built-in functions that would enable you to send information to some API using http protocol. If you would need to call some REST endpoint or a web hook from the T-SQL code, you would need to use WebClient or WebRequest ......(more)

Finding overlapping ranges of data - This week, I had a problem where I needed to find and eliminate from the results of my query, data with overlapping ranges. I have written about this topic before, in my database design book book, in regards to building a trigger to avoid overlapping ......(more)

SQL SERVER – Be Careful with Logon Triggers – Don’t use Host_Name - Before you read further, let me warn you that Logon triggers are potentially evil and might also end up locking everyone out of the instance. Be careful! Don’t just copy paste the code and create the trigger, you might end up in downtime to fix the issue. Recently, ......(more)

Tools

The April release of SQL Operations Studio is now available - This post is authored by Alan Yu, Program Manager, SQL Server. We are excited to announce the April release of SQL Operations Studio is now available. Download SQL Operations Studio and review the Release Notes to get started. SQL Operations Studio is ......(more)

Shortcut: Fix Intellisense and Printer Colors in SQL Server Management Studio - SQL Server Management Studio (SSMS) is a highly configurable tool. One of the areas that’s often ignored but which can be quite important is color configuration. SSMS color codes SQL scripts (and other types of files that it understands) as you type. This ......(more)

The Lighter Side

An ode to my alarm clock - In this not-so-typical post, I describe how hard it is to say goodbye to my favorite piece of technology over the last 21 years. ...(more)

Testing Software

Database testing and ETL processes - Unit testing using tSQLt is really great, you call FakeTable on any tables your code touches and then insert the columns and rows your test actually need, nothing more, nothing less but what happens when you have more complicated processes like an ETL ......(more)

SQL Server on Linux

SQL Server 2017 on SUSE Enterprise Linux Server offer - This post is authored by John ‘JG’ Chirapurath, General Manager, Azure Data. Since SQL Server 2017 on Linux was made generally available on October 2, 2017, we have seen tremendous growth in adoption. We’ve had ~ 5 million Docker pulls for SQL Server ......(more)

SQL Server News

Take the SQL Server Mac challenge - When I graduated from college, one of the first computers I ever used was a MacIntosh. I loved the Mac, the user interface, and the overall footprint of that computer. I also started my career developing on UNIX systems with C++ and databases like Ingres. ...(more)

SQL Server 2016 Service Pack 2 is now available - The SQL Server team is excited to bring you the second service pack release for SQL Server 2016. The service pack is now available for download on the Microsoft Download Center and will be coming soon to Visual Studio Subscriptions, MBS/Partner Source, ......(more)

SQL Server 2017 Cumulative Update 6 - On April 17, 2018, Microsoft released SQL Server 2017 CU6, which is Build 14.0.3025.34 I count 39 fixes or improvements in the public fix list. Remember, there are not going to be any Service Packs for SQL Server 2017. We are still in the monthly release ......(more)

Microsoft offers limited-time 30 percent discount on SQL Server on Linux - Microsoft is offering 30 percent off a SQL Server 2017 on Linux subscription through June 30, 2018, to qualifying customers. ...(more)

Software Development

SQL Ops Studio extension development : Starting an adventure - I’ve been in technology a long, long time (more than 2 decades). Over the years, I learned Access which took me to Oracle and then translated nicely to SQL Server where I’ve spent the bulk of my time. Along the way different projects had different requirements ......(more)

Security news and thoughts

Researchers Find Amazon Alexa Can Be Hacked to Record Users - Security firm Checkmarx reports that malicious Amazon Alexa skills could have enabled an attacker to record everything a user says. Amazon has taken steps to mitigate the issue. ...(more)

Top Fed IT Contractors Are Leaving Emails Unsafe - Cybercrime watchdogs at the Global Cyber Alliance studied the top IT contractors used by the U.S. government and found troubling results. According to the GCA research announced today, out of the 50 top federal IT contractors, only one is using DMARC ......(more)

Forged Email Headers Strike Fear In Gmail Users - Gmail users got a bit of a shock when they discovered over the weekend that "they" had sent spam emails to themselves. ...(more)

Careem Data Breach Exposes Data Of 14 Million Users - Three months after Careem's servers were breached exposed the data of 14 million drivers and riders, the company made the incident public. ...(more)

When you go to a security conference, and its mobile app leaks your data - Enlarge / Screenshots of the RSA Conference application from the Google Play Store. The app's Web interface leaked attendee data when supplied with a token obtained by registering the app. (credit: Google Play Store ) A mobile application built by a ......(more)

Replication

SQL SERVER – FIX: Unable to Create Distribution Database on a UNC Share – Configure Distribution - Whenever we try to use Configure Distribution option in SSMS in a SQL Server instance which is configured on a UNC Path, the wizard will not allow us to configure the Distribution database and we will get the below error: The paths to the distribution ......(more)

Replication Enhancement – Distribution Database in Availability Group - SQL Server replication uses the publisher, distributor, and subscriber paradigm to provide logical data replication capability between different SQL Server instances, and sometime with heterogeneous data source or data destination. Replication uses the ......(more)

R Language

Big changes behind the scenes in R 3.5.0 - A major update to R is now available. The R Core group has announced the release of R 3.5.0, and binary versions for Windows and Linux are now available from the primary CRAN mirror. (The Mac release is forthcoming.) Probably the biggest change in R ......(more)

Anomaly Detection in R – The Tidy Way - In Data Science, As much as it is important to find patterns that repeat, It is also equally important to find anomalies that break those. This is actually very important in a place where we’ve got Time Series Data. Time Series Data is one where the ......(more)

Python in SQL Server

How to make Seaborn Pairplot and Heatmap in R (Write Python in R) - Before you judge me, let me confirm it that ggplot2 is amazing. But there are a couple of plots that I admire in Python’s modern Data Visualisation library Seaborn. ...(more)

Topic Modeling in Python with NLTK and Gensim - In this post, we will learn how to identify which topic is discussed in a document, called topic modeling. In particular, we will cover Latent Dirichlet Allocation (LDA): a widely used topic modelling technique. And we will apply LDA to convert set of ......(more)

Product Upgrades and Releases

Released: System Center Management Packs for SQL Server 2008-2016 (7.0.4.0) - We are happy to announce that updates to SQL Server Management Packs (2008-2016) have been released. Downloads available: Microsoft System Center Management Pack for SQL Server 2016 Microsoft System Center Management Pack for SQL Server 2014 Microsoft ......(more)

Released: Microsoft Azure SQL Database Management Pack (7.0.4.0) - We are happy to announce the release of Azure SQL DB Management Pack. You can download the management pack and find a summary of features at: Microsoft System Center Management Pack for Microsoft Azure SQL Database New Features and Fixes Fixed issue: ......(more)

PowerShell

Finding linked server references using PowerShell - It's 10 PM: do you know how many of your database objects contain linked server references? ...(more)

three ways to track logins using dbatools - Years ago, I wrote Watch-DbaDbLogin which keeps an inventory of accounts, hosts and programs that log into a SQL Server. It was pretty crude, but helped immensely during my migration, as this inventory ensured that my documentation was in order and no ......(more)

PowerShell – How to Install dbatools? - One of the most popular questions any PowerShell expert would receive is how to install the most popular dbatools based on Powershell scripts? Well, first of all, I was never a PowerShell fan, to begin with as I believed it as a totally different language ......(more)

Checking Tempdb with dbatools - I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items. In ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Reports and Buttons (new with Power BI Desktop – April 2018) - In this video, Patrick looks at Power BI reports and buttons. Buttons were added to Power BI Desktop in the April 2018 release. You can now add buttons to your Power BI reports and configure actions for when they are selected. Bookmarks and selections ......(more)

Thoughts and Lessons Learned From A Power BI Embedded POC - I worked on a Power BI embedded POC where a report with an in-memory Power BI model as the dataset was embedded into an application in an “app owns data” scenario. This means that the application handles all authentication and access, and users do not ......(more)

Time Intelligence in Power BI – Global Power BI User Group - Time Intelligence is probably the most interesting feature of any analytical solution. Computing Year-To-Date, Month-To-Date, Same-Period-Last-Year, is quite easy in DAX but, as soon as the customer requests for time intelligence comparing data from ......(more)

Dynamically Changing A Chart Axis In Power BI Using Bookmarks And Buttons - A very common requirement when building Power BI reports is to allow the end user to change what is displayed on a chart axis dynamically. A lot of people have blogged about how to do this – Kasper’s blog post here is a great example – but the problem ......(more)

Power BI Custom Visuals Class (Module 99 – Drill-Down Donut Chart - In this module you will learn how to use the Drill-Down Donut Chart. The Drill-Down Donut Chart allows you to visualize categorical data in an interactive drill-down chart. Module 99 – Drill-Down Donut Chart Downloads Power BI Custom Visual – Drill-Down ......(more)

Power BI parameters – Updating in the Power BI Service or with APIs - In this video, Adam looks at how you can update Power BI parameters within the service or by using APIs. You can create Power BI Parameters within Power BI Desktop using Power Query. Then you can update those Power BI parameters once the report is published ......(more)

Dabbling in Distributions Part 2 - One playoff game down, three to go. So far, so good for my Vegas Golden Knights! Not only did we win the first game, but it was also a shutout! No score for the Kings! (And no doughnuts for me – I’ve been focused on cutting out carbs and sugar this ......(more)

Performance Tuning SQL Server

SDU Tools: Update Statistics on SQL Server Tables - Having up to date statistics is critical for SQL Server choosing appropriate query plans. Poor statistics can lead to poor query plan outcomes. Generally, SQL Server manages this well by itself. As a rough rule, most versions auto-update statistics when ......(more)

Concurrency Week: How Entity Framework and NHibernate Can Cause Poison RESOURCE_SEMAPHORE Waits - I’ve already blogged about my dislike for ORMs from a production DBA performance tuning standpoint only. I get that they’re useful to developers. I get it. But I’m focused on performance. A quick recap of what I don’t like about ORMs from that other ......(more)

Execution Plan Shortcoming in Extended Events - I use Extended Events almost exclusively for capturing query metrics. They are the most consistent and lowest cost mechanism for getting the time and resources used by a query. They can be filtered, combined with other events, they’re just marvelous… ......(more)

Parking Garages And Predicates - Hotels on Wheels Parking garages and predicates have a “lot” in common. GET IT? No no but seriously. They do. If you’ve ever lost your ticket, forgotten where you’ve parked, or just driven around hopelessly waiting to see an empty spot, you probably ......(more)

Index Key Column Order And Supporting Sorts - Whatever Man When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access ......(more)

Does an Index on Just The Clustering Key Ever Make Sense? - Say you’ve got a table – in this case, I’m using the Stack Overflow Posts table, which holds all of the questions and answers: StackOverflow.dbo.Posts See the clustered index at the bottom? It’s on the Id field, which is an identity – starts at one and ......(more)

SQL: Does having more indexes always make data modifications slower? - As I've pointed out in other posts, Betteridge's Law of Headlines says you already know that the answer to this is no. There is a persistent myth among SQL Server users that having more indexes always slows things down, particularly anything that needs ......(more)

Microsoft News

OneNote desktop app end-of-lifed, replaced with Windows 10 UWP - While the main Office apps remain traditional desktop Windows applications, Microsoft has been developing a modern version of OneNote using the Universal Windows Platform (UWP) APIs for some years; it's arguably ......(more)

Hardware News

Samsung Announces 970 PRO And 970 EVO NVMe SSDs - Samsung has announced the third generation of their high-end consumer NVMe SSDs. The new 970 PRO and 970 EVO M.2 NVMe SSDs use a newer controller and Samsung's latest 64-layer 3D NAND flash memory. The outgoing 960 PRO and 960 EVO were first announced ......(more)

Western Digital Launches Ultrastar DC HC530 14 TB PMR with TDMR HDD - On Wednesday, Western Digital introduced its highest capacity hard drive based on conventional magnetic recording to date. The HGST Ultrastar DC HC530 can store 14 TB of data and uses perpendicular magnetic recording (PMR) with two-dimensional magnetic ......(more)

The AMD 2nd Gen Ryzen Deep Dive: The 2700X, 2700, 2600X, and 2600 Tested - With 2017 finished, and Ryzen being very successful for AMD, the inevitable question was due: what happens next? Early in 2018, the plans were laid bare: a second generation Ryzen processor was set to come in mid-year, followed by a second generation ......(more)

HA/DR/Always On/Clustering

Choosing a Disaster Recovery Technology: Backup vs. Replication - Backup and replication are two of the most fundamental availability and disaster recovery (DR) technologies. While many businesses use these technologies, there’s a lot of confusion about their different roles for high availability and disaster recovery. ...(more)

SQL SERVER – Steps to Deploy Distributed Availability Group – Windows Clusters in Different Domains - In the current era where companies’ mergers and take over is a common phenomenon, it is possible to run into a situation where there are two domains which are connected but not trusted. I was engaged in consulting for a client who had a similar merger ......(more)

Why Don't I have a Preferred Replica? - https://imgflip.com/i/28n8j8--After some investigation we found that while the Availability Group was configured to prefer the primary replica for backups, it wasn't evaluating as the preferred backup via the system function sys.fn_hadr_backup_is_preferred_replica!What ......(more)

Graph Databases

SQL Server Graph Databases – Part 4: Working with Hierarchical Data in a Graph Database - The series so far: SQL Server Graph Databases – Part 1: Introduction SQL Server Graph Databases – Part 2: Querying Data in a Graph Database SQL Server Graph Databases - Part 3: Modifying Data in a Graph Database SQL Server Graph Databases – Part 4: ......(more)

DevOps and Continuous Delivery (CI/CD)

DevOps: To branch or not to branch - One of the discussions that comes up from time to time when working with DevOps is branching strategy. One of the main features of Git that's often claimed is that it's so good at branching and merging. And indeed, it's pretty good at that. But the bigger ......(more)

Lack of Database Deployment Automation Creates ‘App Downtime’ - Increased automation in database deployments could accelerate app release cycles while helping developers more effectively find and fix database errors, according to the findings of a survey sponsored by Datical. ...(more)

The future of database DevOps - Foundry is Redgate’s R&D division. Part of our remit is to look further into the future. We do that by keeping an eye on things that might indicate a change in what you need from our products (eg, a new problem), or a development in technology (eg, a ......(more)

DevOps: Scripting SQL Server objects and data from the command line - The other day I posted a shortcut about how to use SQL Server Management Studio (SSMS) to generate INSERT statements for data. In one of the comments, Tom Corrigan asked if there was a way to do that from the command line. The answer is yes. Apart from ......(more)

Database Design, Theory and Development

Read committed doesn’t guarantee much… - A while back I was involved in an email thread where people were wondering about some ‘weird’ behavior from SQL Server. The problem was occurring on SQL Server 2016 and used the default isolation level of read committed. The scenario was as follows: Create ......(more)

 GUID or Integer Primary Key? If you are designing a new data model or maybe you are looking to improve an existing one.... - GUID or Integer Primary Key?If you are designing a new data model or maybe you are looking to improve an existing one.  One of the key decision you would be making is what should be the primary key in each table.Now first rule or best practice is that ......(more)

Data Science

Analytics Translator vs. Citizen Data Scientist: What is the Difference? - Click to learn more about author Kartik Patel. There is a new business role on the horizon and, at first glance, it may seem very much like a role that was introduced a few short years ago. This new enterprise role is known as an ‘Analytics Translator’ and, ......(more)

Data Privacy and GDPR

So what is data mapping and why is it the key to GDPR compliance? - The GDPR brings new responsibilities for organizations which need to store and process personal data. Plan for it in the right way, however, and the journey to compliance will be a lot easier....(more)

So, What is Data Mapping and Why is it the Key to GDPR Compliance? - Click to learn more about author Richard Macaskill. The first three posts in this series on GDPR are: So, What is GDPR and Why Should Database Administrators Care? So, What is GDPR, and Why Should Your Customers Care? So, What is a Data Protection Impact ......(more)

Masking Data in Practice - This article describes the practicalities of data masking, the various methods we can use, and the potential pitfalls....(more)

Facebook Expands GDPR Privacy Protections Outside Europe - Facebook announced that it plans to introduce many of the privacy changes required by the General Data Protection Regulation (GDPR), which goes into effect on May 25, to users around the world. ...(more)

Is Rapid Discovery of ERP or CRM Personal Data for GDPR possible? - Click to learn more about author Roland Bullivant. One of the key tasks early in a GDPR compliance programme is to locate where Personal Data is stored across an organisation’s information ecosystem. Often this is referred to as an Information Audit ......(more)

Conferences, Classes, and Events

SQL Saturday Dallas 2018 - Hey all! I am doing my DBA Fundamentals all day pre-conference at SQL Saturday Dallas on Friday, May 18.  Register HERE. $120 until May 1, then $140.  If you are not sure if this is the class for you, please go watch this promo video I threw together, ......(more)

New All-Day Session: Designing Modern Data and Analytics Solutions in Azure - At the fall 2018 PASS Summit in Seattle, I'm excited to be co-presenting a full day pre-conference session with my good friend & fellow BlueGranite colleague Meagan Longoria. Why Do a Pre-Con? I have a natural inclination to share information that I ......(more)

Career Growth

5 ways to increase productivity in cross-functional dev teams - With the craze over building Single Page Applications (SPAs), companies have joined the movement and hired armies of front-end engineers with/without experience in back-end technologies....(more)

Burnout, Self-Care and Resilience - Reading time ~3 minutesFirst, a thankyou to Ceedee in New Zealand for prompting me to write again. It’s been far too long and I love doing it. A very personal post today. (The next few posts after this will be back to agile, leadership, and management ......(more)

Dear SQL DBA: How Do I Prepare for Certification Exams? (video with captions and transcript) - In this episode, I talk about how to strategize for and prepare for Microsoft Certification exams, using the Database Fundamentals exam as an example. Want to attend a live recording of the Dear SQL DBA podcast? It was so much fun to record this episode ......(more)

Big Data

Big Data, Make Room for Precision Data - Click to learn more about author Justin Schweisberger. In the six years or so since it started to grab headlines, Big Data has made it to the mainstream, and businesses are starting to report tangible results. Most large organizations now have Big Data ......(more)

Backup and Recovery

Accidentally deleted all my production data in Azure Table Storage!!! What now? - So you moved to the Cloud and you fully understand Azure Storage and all the greatness that comes with the various replication options i.e. Locally redundant storage (LRS), Zone-redundant storage (ZRS), Geo-redundant storage (GRS).   All these replication ......(more)

SQL SERVER – Script – Transaction Log Backups Based on Growth / Size - While doing a comprehensive health check of the SQL Server, one of the things I check is the backup plan for the production databases. Many times, I have run into a situation where a customer says “Transaction log file of the database has grown more ......(more)

Azure SQL Database

Getting the Azure DTU Calculator to work on a 2008R2 OS w/ a 2008R2 SQL Server Instance - Hello everyone!  I recently had to do an analysis of a client’s database workload using the Azure DTU Calculator(DTU Calculator) and thought it might be interesting to share just how I did that.  I have run this tool numerous times on other clients via ......(more)

Azure SQL Database – scalability - Azure SQL Database is a flexible Platform as a service database that can be easily scaled to fit your needs. You can add more compute or storage to satisfy your performance requirements without waiting for new hardware or migrating data to more powerful ......(more)

How Azure SQL Database makes database management easier? - Azure SQL database is a platform as a service SQL Server Database Engine hosted in Azure cloud and managed by Azure. Azure automates many management activities and enables you to focus on development and optimizations. In addition, it enables you to ......(more)

State of the Cloud Database: April 2018 - It’s an exciting time to be a database professional. The technology is advancing quickly, large datasets are easier to handle than ever before, and the cloud is opening up new functionality and use cases that we couldn’t tackle before. My fellow database ......(more)

Modernize to cloud with Azure SQL Database - Companies are embracing digital transformation by migrating to the cloud. IT organizations are continuously looking for new ways of optimizing how they use the data that fuels their operations. Modernizing to the cloud offers the right mix of operational ......(more)

Analysis Services / BI on the MS Stack

Columnstore became default in SAP BW - Overview The following features have been optionally available in SAP BW on Microsoft SQL Server for several years: SQL Server Columnstore for BW cubes (as of SAP BW 7.0) Columnstore Optimized Flat Cube (as of SAP BW 7.4) See also https://blogs.msdn.microsoft.com/saponsqlserver/2018/04/26/flat-cube-became-default-in-sap-bw The ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning vs. Deep Learning - The debate on Machine Learning vs. Deep Learning has gained considerable steam in the past few years. The fundamental strength of both these technologies lies in their ability to learn from available data. Though both of these offshoot AI technologies ......(more)

Azure ML Package-Publish a Web service from R-Part2 - In the last post, I have explained, how to use AzureML package with the aim of exploration of Azure ML studio experiments and datasets inside R studio environment. moreover how we can use this package to upload a dataset from R studio into Azure ML has ......(more)

Feedzai Launches OpenML, an Open Machine Learning Engine to Fight Fraud - by Angela Guess According to a recent press release, “Feedzai has launched the Feedzai OpenML Engine, in response to recognizing the need for data science teams to utilize their own tools and expertise. Built on Feedzai’s distributed microservices architecture, ......(more)

Administration of SQL Server

SQL Server 2016 – SQL Server Telemetry \ CEIP Services - CEIP (Customer Experience Improvement Program) is somethings runs from ages and almost each vendor perform it to collect product usage data, issues faced and other information. Vendor uses this information for market expansion, bug fixing and new features. But ......(more)

How to get alerted anytime SQL Service is restarted? - You may and should have monitoring in place to monitor state of your servers, services, jobs, critical and not critical errors etc.Here I just wanted to share a quick script to create a SQL Server agent job to alert you whenever SQL Server service is ......(more)

Database Files Down The Wrong Path - I manage a few servers used to host SQL Instances for development and test purposes. Each of those instances hosts databases covering multiple environments. So I’ve got multiple servers, with multiple instances, with multiple environments. It’s important ......(more)

Replacing Trace Flags 1204 and 1222 with the Extended Events system_health Session - Deadlocks in a SQL Server instance are problematic. They can cause application errors, slow performance, and unhappy users. As a DBA or developer, it’s very helpful to be able to find deadlocks, review what caused them, and fix it permanently, if possible. How ......(more)

How to Perform Database Hack-Attach - This blog demonstrates attaching a database on the SQL Server Instance which already has the same name database up and running. Scenario: Suppose, you got a hardware migration activity. The migration activity involves moving the databases from old hardware ......(more)

How to start SQL Server job using TSQL and wait for it to complete? - If you ever had a need to start a SQL job using sp_start_job stored procedure in msdb then you know once the job is started successfully, it returns the control back to the user immediately and does not wait for the job to complete.But what if you want ......(more)

SQL server database size, location and volume info - For one of my clients, I needed to know and document not only database names, size and locations but also disk volume information as well.Here is a query I came up with to retrieve all that using a single script.-- SQL SERVER VERSION 2005 & UP/*Caveat: ......(more)

Easy to use WMI commands to query windows system - WMI has been around for a while and it has grown significantly over the years. With Microsoft's focus on powershell, WMI is maybe getting less visible in the public eyes.This is not to say I am not a fan of powershell. But it can be bit daunting to get ......(more)

Command shell steps in SQL Agent jobs only process a single command. - I was working on a SQL Agent job recently that required a series of command shell commands. And it would not work. Days I spent fighting it. I eventually went to sqlhelp. And while no one was able to give me an easy answer directly, Randolph West (b/t) ......(more)