SQL Server Expert Blogs

Database Configuration Management for SQL Server

Simple-Talk on SQL - Tue, 11/04/2014 - 17:00

It is not just the rapid and painless testing, deployment and update of databases that requires care in the retention and management of configuration information. Configuration information is also essential for audit, resilience, and support. The range of documentation varies widely with the database and its setting, but the underlying principles remain the same. Without appropriate configuration management, automation is likely to be futile.

Questions About Using TSQL to Import Excel Data You Were Too Shy to Ask

Simple-Talk on SQL - Tue, 11/04/2014 - 17:00

It is easy to import Excel data into database tables via TSQL, using OLEDB, either by the OPENROWSET function or linking to the spreadsheet as a server. The problem is that there are certain things that aren't obvious that you need to know about, and you feel awkward about asking such simple questions.

SSIS 2012 Projects: Setup, Project Creation and Deployment

Simple-Talk on SQL - Mon, 11/03/2014 - 17:00

It used to be that SQL Server Integration Services (SSIS) packages had to be deployed individually. Now, they can be all deployed together from a single file by means of the Project Deployment Model introduced in SSIS 2012. Where there are tens or hundreds of SSIS packages to deploy, this system is essential. Feodor Georgiev talks us through the basics in the first of a three-part series.

Where in the Application Should Data Validation be Done?

Simple-Talk on SQL - Mon, 11/03/2014 - 17:00

Whereabouts in the application should the business logic of data-validation checks be made? The advantages of a layered approach to validation that includes database constraints, would seem to outweigh the disadvantages. William Sisson explains some interesting issues.

Can you restore from your backups? Are you sure?

Tibor Karaszi - Mon, 11/03/2014 - 10:52
A few days ago, we were doing restore practice drills with a client. I had tested the stuff before this, so the practice was more for the client's DBAs to test various restore scenarios, with me being able to point to the right direction (when needed),...(read more)

Ola Hallengrens Maintenance Solution now supports mirrored backup

Tibor Karaszi - Mon, 11/03/2014 - 06:27
You probably know that you can mirror a backup to several destinations, assuming you are on a supported edition (Enterprise or Developer). This is not the same as striping; you can compare striping to RAID 0, and mirroring to RAID 1. Ola now supports...(read more)

Hekaton in 1000 Words

Simple-Talk on SQL - Tue, 10/21/2014 - 17:00

The SQL Server 2014 In-Memory OLTP engine (a.k.a. Hekaton) is a radical change for relational databases. This article, an exerpt from Kalen Delaney's book "SQL Server Internals: In-Memory OLTP", provides a brief overview of what Hekaton is and why it's important.

The Mindset of the Enterprise DBA: Delegating Work

Simple-Talk on SQL - Thu, 10/16/2014 - 17:00

A lot of the routine jobs demanded of a DBA can be automated, but a tougher prospect is to automate these jobs in a way that the requestor rather than the DBA can actually set of the job running themselves without compromising security and without risk. Is it true to say that some tasks can be made self-service? In the final part of his series, Joshua considers delegation.

Exploring Your SQL Server Databases with T-SQL

Simple-Talk on SQL - Wed, 10/08/2014 - 17:00

Most DBAs hoard their own favourite T-SQL scripts to help them with their work, often on a USB 'thumbdrive', but it is rare that one of them offers us a glimpse of the sort of scripts that they find useful. It is our good fortune that Scott Swanberg shows us those scripts he uses for discovering more about database objects.

Set-based Constraint Violation Reporting in SQL Server

Simple-Talk on SQL - Mon, 10/06/2014 - 17:00

When you're importing data into an RDBMS in bulk and an exception condition is raised because of a constraint violation, you generally need to fix the problem with the data and try again. The error won't tell you which rows are causing the violation. What if you've thousands of rows to search when it happens? There are solutions, writes William Sisson.

Questions about Primary and Foreign Keys You Were Too Shy to Ask

Simple-Talk on SQL - Sun, 10/05/2014 - 17:00

It is strange that one can ask simple questions about extended events or Hekaton at professional events and conferences without feeling embarrassed, yet nobody likes to ask vital questions about SQL Server primary keys and foreign keys. Once more, Rob Sheldon is 'drawn to one side' to answer those questions about keys that one is too shy to ask.

Improving the Quality of SQL Server Database Connections in the Cloud

Simple-Talk on SQL - Sun, 09/21/2014 - 17:00

To access SQL Server from the client, you use TDS protocol over TCP. This is fine over reliable LANs but over the internet these connections are relatively slow and fragile, TDS is still used to connect to databases in the cloud, but you need to use a combination of the new features such as connection pools and idle connection resiliency to make applications faster and more reliable.

Filling In Missing Values Using the T-SQL Window Frame

Simple-Talk on SQL - Wed, 09/17/2014 - 17:00

Since SQL Server delivered the entire range of window functions, there has been far less justification for using the non-standard ex-Sybase 'Quirky Update' tricks to perform the many permutations of running totals in SQL Server. One of these related problems is the 'Data Smear'. Do window functions make this easier, and what is performance like? Dwain Camps investigates

Importing Excel Data into SQL Server Via SSIS: Questions You Were Too Shy to Ask

Simple-Talk on SQL - Wed, 09/10/2014 - 17:00

The job of retrieving data from Excel, and importing it into SQL Server hasn't the same appeal or glamour as, for example, performing heroics with ill-performing queries. This could be why one hesitates before asking questions about how to do it. Rob Sheldon calms your private doubts and fears by answering those embarrassing questions.

SQL Server Performance Troubleshooting with SQL Monitor 4

Simple-Talk on SQL - Tue, 09/09/2014 - 17:00

Any SQL Server monitoring tool must gather the metrics that will allow a DBAs to diagnose CPU, memory or I/O issues on their SQL Servers. It should also provide a set of accurate, reliable, configurable alerts that will inform the DBA of any abnormal or undesirable conditions and properties, as well as specific errors, on any of the monitored servers. This article provides an in-depth guide to the monitoring and alerting functionality available in one such tool, Red Gate SQL Monitor. It focuses on the latest edition (4.0), which includes several key new features, such as performance diagnosis using wait statistics, the ability to compare to baselines, and more.

The Mindset of the Enterprise DBA: Harnessing the Power of Automation

Simple-Talk on SQL - Mon, 09/01/2014 - 17:00

After you have done the necessary groundwork of standardizing and centralizing your database administration processes, you are now in a position to implement some effective automation of some of these processes. Which ones do you choose, and how do you set about automating these tasks?

Who Broke the Build?

Simple-Talk on SQL - Thu, 08/28/2014 - 17:00

Continuous Integration and automatic builds are fantastic tools for software teams, but only if developers take responsibility for their code. Karsten Kempe explains how to use Team Foundation Server to drive better continuous integration, and walks through a simple (open source) tool he built to make nightly builds more transparent, and more valuable.

SQL Server Reporting Services Basics: Customizing SSRS Reports

Simple-Talk on SQL - Tue, 08/26/2014 - 17:00

While the Reporting Wizard and basic report tables will do for prototyping, very soon we'll want to add some finesse to our reports, and allow our report users to create different views of the same data, dynamically. Kathi Kellenberger shows how to use expressions, functions, grouping, the matrix control, and other features, to add a degree of user control to the reports while maintaining visual clarity.

Questions about T-SQL Expressions You Were Too Shy to Ask

Simple-Talk on SQL - Tue, 08/12/2014 - 17:00

Nobody seems to ask questions about SQL Expressions in forums, even though expressions can cause all sorts of problems. Even the books on T-SQL skate over them in haste to get to more complicated topics. It is time for frank, straight-forward Q&A, and who better than Robert Sheldon to give the A?

Quickly Investigating What's in the Tables of SQL Server Databases

Simple-Talk on SQL - Mon, 08/11/2014 - 17:00

From SQL Server Management Studio it is difficult to look through the first few rows of a whole lot of tables in a database. This is odd, since it is a great way to get quickly familiar with a database. Phil was persuaded to tidy up a SQL routine he uses to investigate databases quickly in a browser. He explains how to use it, how it works, and how to use it from PowerShell.