2016-08-31

Data Wrangling: Linked Servers and the DBA

SQL Server Linked Server

In my previous post, I defended the use of linked servers for administrative purposes. Now let's look at some practical uses. The basic concept is to connect to the CMS, iterate through sys.servers, select data from the remote servers, then return the data for all linked servers as a single data set. Let's begin with an example that returns a list of databases on all of our linked server SQL instances.

2016-08-28

In Defense Of Linked Servers

SQL Server Linked Server

I seem to be in the minority when it comes to SQL Server linked servers. When it's another SQL Server instance on the other end, I quite like them for administrative purposes. But other SQL pros have some reservations and gripes. I've even seen the word "hate" thrown around freely.

2016-08-15

It Takes A Village To Secure Our Data

SQL Server Authorization

If you've ever given any consideration to IT security, you've probably heard of the layered security model. It's often explained with an onion analogy: at the center are your assets, surrounded by multiple layers (security controls) protecting them. Those assets are often your company's data in a relational database management system (RDBMS), such as SQL Server. One of the last lines of defense against unauthorized access to your data is the security configuration of the RDBMS. It is what lets the good guys in and keeps the bad guys out. This is not something to be taken lightly.

2016-08-11

Broken Backup Chains

SQL Server Broken Backup Chain

There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:

2016-07-31

Responsible SQL Server Alerts

SQL Server Alerts

I recently had a transaction log fill up, which generated a bunch of Severity Level 17 alerts. Thousands, in fact, over and over and over... For many of you, this will probably look familiar:

2016-07-12

T-SQL Tuesday #80

T-SQL Tuesday

For T-SQL Tuesday #80, Chris Yates (b|t) asks "Perhaps there is a feature you would like to see added into SQL that just isn’t there yet – what is it?". That's a great question!

2016-07-04

SQL Server Event Handling: DDL Events

SQL Server Event Handling: DDL Events

For many SQL Server professionals, our first overt need to handle an event was for a data-related action (ie an insert, update, or delete). Naturally, we use triggers to handle these events. Or more specifically, DML triggers. And if you are familiar with them, working with DDL triggers should be familiar too.

2016-06-28

SQL Server Event Handling: Startup

SQL Server Startup

The startup of an instance of SQL Server is a rare event. It only happens once for the life of the database engine process (sqlservr.exe). The timing of the event makes it appealing, though. It's an ideal opportunity to run T-SQL initialization tasks or other T-SQL tasks that check various environment settings. By taking advantage of the startup event, a DBA is often able to find problems before the problems find her.

2016-06-26

Introduction To SQL Server Event Handling

When I think about SQL Server "events", I can't help but approach it from a developer's perspective. Consider this example of connecting to SQL Server from SSMS:

2016-06-21

SQL Server: Rolling Your Own Alerts

If you're familiar with SQL Server Alerts, this will probably look familiar:

SQL Server Alerts