SQL Server News for 2012-10-22

Make SQL effortless
SQL Prompt 5.3 is packed with features to make writing, editing, and exploring database code effortless. From code completion to SQL reformatting, SQL Prompt 5.3 handles the routine so you can focus on the tricky bits. Try it now.

Are you sure you can restore your backups?
Run full restore + DBCC CHECKDB quickly and easily with SQL Backup Pro's new automated verification. Check for corruption and prepare for when disaster strikes. Try it now.

Editorial - Open Data

San Francisco passed an Open Data law a few years ago. The idea was for the goverment to make a reasonable effort to make data about the city, it's services, and more, publicly available. This was supposed to make government more accountable and more transparent, but also allow toold to be built that could help the citizens or the government itself.

Since then, more governments have opened up their data, and the experiments are growing. Various people have built applications for mobile devices that help citizens find and use services, and there are a few businesses that are using the data.

Is this important for you as a data professional outside of San Francisco? I think it gives you an opportunity to work with data, and even build some tool yourself that might perform analysis for visualization. You have the chance to use the data to showcase your talent, or even practice your query skills in looking for patterns or mining the data.

There are any number of uses for this data, but not the least of which is a personal project that you take pride in, and show off in your next interview or review. You might even find ways to take those skills and apply them to data inside your company.

The Weekly News

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.

San Francisco looks to tap into the open data economy - As interest in open data continues to grow around the world, cities have become laboratories for participatory democracy. They’re also ground zero for new experiments in spawning civic startups that deliver city services or enable new relationships between the people and city government. San Francisco was one of the first municipalities in the United States to embrace the city as a platform paradigm in 2009, with the launch of an open data platform....(more)

Blogs : Administration

SQL Server Installation Best Practices Video - Wait! You only get one chance to do this right. Before you run setup.exe, there are some basic configuration steps that will make all the difference in performance and troubleshooting. Brent Ozar will explain his SQL Server Setup Checklist covering the page file, service accounts, memory settings, and hardware stress testing. We won’t cover scripted installations or slipstreaming — this half-hour session is for folks who click through the setup GUI because they only install a few servers per year....(more)

MDS: Copying models and data to another server - Having recently completed a model in Master Data Services (MDS), I needed to move the model from a development environment to a QA environment. Below is some helpful tips if you need to do the same, along with details on a bug I ran across....(more)

Another Stop-Gap Solution for the SQL Server 2012 Identity Crisis - Back on April 26, Grant Fritchey (of execution plan fame) reported a strange bug in the newly released version of SQL Server, in which the values of certain identity columns are suddenly incremented by about 1,000. Since then, 36 other users have confirmed the problem and 15 have posted in the thread at Microsoft Connect titled Failover or Restart Results in Reseed of Identity....(more)

Querying database usage - A question that a DBA will often be asked is what databases are actively being used on my SQL Server? We get asked it all the time, usually as part of a consolidation or rationalisation project....(more)

Inserted and Deleted are One Table? - Inserted and deleted tables are 2 pseudo tables preseting the before and after images of modified rows. They can only be accessed from within triggers. Those two have been around for quite long time but it’s hard to find any articles to tell how they are populated. Some rumors said they are retrieved from transaction logs in SQL Server 2000. Recently, I was asked to evaluate the performance impact of using trigger for my customer’s project. I did some investigation and got some interesting results. ...(more)

Important Alerts for SQL Server - ne of the things that I think is extremely important for DBAs and really anyone that has to administer a SQL Server instance is a set of alerts on various items that might occur....(more)

SQL Server 2008 Diagnostic Information Queries (Oct 2012) - Here is the October 2012 version of my SQL Server 2008 Diagnostic Information Queries, with some minor tweaks and improvements to a number of the existing queries, and one new query. To go with it is an updated, blank 2008 results spreadsheet, where the tabs in the spreadsheet are labeled and in the same order as the queries. The idea is that you can run these queries one by one, click on the top left square of the results grid, and then right-click and select “Copy with Headers” to select and copy the results of the query and then paste them into the matching tab of the results spreadsheet. ...(more)

Index Definition Audit Script - Recently, I needed to audit indexes on two different servers. I wanted to compare things like index keys and included columns, partitioning keys, unique constraints, clustered indexes and primary keys, and filter index definitions. Basically, I wanted to make sure that the indexing of two databases on two different servers were completely in sync. To do this, I wrote the following scripts. The first script will audit a single database or even a single table. The second script once more makes use of Aaron Bertrand’s sp_foreachdb procedure to iterate through every database on a server. ...(more)

Survey: do you have a baseline? - For the last few weeks I've been thinking about performance baselines, mostly because I've been producing Erin's new Pluralsight course on Benchmarking and Baselining. It got me thinking that it would be interesting to know how many of you are using baselines, and what for, and if not, why not. ...(more)

Blogs : Career

DBA Study Guide - Hello Dear Reader, over here at Pragmatic Works we’ve been growing like weeds. For the most part we are looking for Sr level people for Sr. Level positions. Part of that process is interviewing. Going for a job as a Sr. Consultant is a bit different than going for a job as a DBA.
Today’s market for DBA’s is quite good. If you are looking there are jobs out there. A lot of the time after weeks or months of interviews when the “ideal” candidate has not been found you tend to lower the requirements. It’s the Animal House “We need the dues” moment. ...(more)

Blogs : Computing in the Cloud

Six Steps to Windows Azure - In January (2012)I delivered the hugely fun Six Weeks of Windows Azure. There was a lot right in the format we went with as well as improvements and changes we could make/try – I even did a little retrospective on it....(more)

Blogs : Database Design, Theory and Development

Designing Databases for Rapid Resilience - As the volume of data increases, DBAs need to plan more actively for rapid restores in the event of failure. For this, the intelligent use of filegroups is important, particularly when the Enterprise Edition of SQL Server offers the hope of online restores. How, though, should you arrange your data on the different filegroups? What happenens if the primary filegroup gets corrupted? ...(more)

Blogs : DMO/SMO/Powershell

Search For a Query in RDL Files with PowerShell - In tracking down poorly performing queries for clients I often encounter the query text in a trace file I've captured, but don't know the source of the query. I've found that many of the poorest performing queries are those written into the reports the business users need to make their decisions. If I can't figure out where they came from, usually years after the queries were written, I can't fix them....(more)

Blogs : Events

SQL In The City: Seattle - If you missed all the great speakers on the five city tour of SQL in the City, don’t despair. Many of the same people will be back at SQL in the City in Seattle. It’s scheduled on Monday before the PASS Summit proper starts, so if you’re looking to get your learn on early and you can’t sign up for a pre-con, this is a great, free, opportunity to pick up some additional instruction. Check out the list of speakers. It’s going to be an event worth attending....(more)

PASS 2012 Countdown – 3 Weeks To Go - Another week down and there are now 3-weeks until this year’s PASS Summit. Take a moment and register now if you haven’t. It’s really easy to do, click on the banner below and enter your information and your manager’s credit card information. She called me earlier today, go ahead and do it. No, really…...(more)

Blogs : High Availability/Disaster Recovery

AlwaysOn Restore Revisited - In my last post I talked about restoring a database that is part of an Availability Group which may have backups taken from multiple instances. The problem is that MSDB is only aware of backups taken from that instance. This means that the Database Recovery Advisor can’t really build the nice pretty timeline if you have backups taken from multiple instances. I have found a way around that problem. When I say “I”, what I really mean is my fellow PFE Denzil found it. Since I mentioned the limitation in my previous post, I wanted to make sure and outline the workaround here. Here is an example scenario:...(more)

Blogs : Integration Services/ETL

SSIS 2012 – Export old version of a deployed project - New in the 2012 version of SSIS is a feature that lets us keep a user-configurable amount of deployed project versions in the catalog. The problem, and the point of this blog post, is that you cannot export an older version of a project without reverting back to that version. This may not be possible in your environment and this post is an effort to provide you with another option....(more)

Create Several Shell SSIS Packages In Minutes Using BIML - What do i mean by shell packages? You’re spot on – that’s an excellent question to begin with. For the purpose of this post, I define shell package as an empty package that is renamed appropriately and contains all the required connection managers....(more)

Blogs : Performance and Tuning

Cardinality Estimation Bug with Key Lookups - Cost-based optimization stands or falls on the quality of cardinality estimates (expected row counts). If the optimizer has incorrect information to start with, it is quite unlikely to produce good quality execution plans. There are many ways we can provide good starting information to the optimizer, and even more ways for cardinality estimation to go wrong. Good database people know this, and work hard to write optimizer-friendly queries with a schema and metadata (e.g. statistics) that reduce the chances of poor cardinality estimation producing a sub-optimal plan....(more)

Blogs : Reporting Services

Presenting Power View Reports to Users - As you begin to use the new Power View reporting feature that is available with the Reporting Services 2012 integration in SharePoint 2010 one of the things that you will need to assess is how you are going to allow the users to access the reports. What are the different options that you have when providing access to the Power View report files (rdlx) in SharePoint?...(more)

Blogs : Security and Auditing

SQL Audit – User-defined Audit Events - was brought to my attention in a comment by one of my readers. He was trying to make use of user-defined events and was having a problem getting the output to actually write to the audit file. Since I was writing some code to recreate the problem on my system anyway, I decided to post it here....(more)

Studying Zero-Day Attacks - resting paper: "Before We Knew It: An Empirical Study of Zero-Day Attacks In The Real World," by Leyla Bilge and Tudor Dumitras: Abstract: Little is known about the duration and prevalence of zeroday attacks, which exploit vulnerabilities that have not been disclosed publicly....(more)

Blogs : Software Development

SQL Server devs–what source control system do you use, if any? (answer and maybe win free stuff) - Recently I noticed a tweet from notable SQL Server author and community dude-at-large Steve Jones in which he asked how many SQL Server developers were putting their SQL Server source code (i.e. DDL) under source control (I’m paraphrasing because I can’t remember the exact tweet and Twitter’s search functionality is useless). The question surprised me slightly as I thought a more pertinent question would be “how many SQL Server developers are not using source control?” because I have been doing just that for many years now and I simply assumed that use of source control is a given in this day and age....(more)

Blogs : T-SQL

When a Covering Index no longer covers - I received an email from my boss on the morning supervisors approve timesheets. He stated that they are complaining about an error: Deadlock. Searching the SQL Log, I was able to get the details of the deadlocking. We have Trace Flags 1204 and 1222 turned on which provides the details. ...(more)

SQL Server # TSQL to Convert STRING in PROPER format - SQL Server has got in-built functions to convert the given string into LOWER() or UPPER() format but it does not provides any direct way to convert it to PROPER format. A string/text is said to be in a PROPER format if all the words in that string/text starts with a CAPITAL letter....(more)

fn_OBJECT_TYPE - I recently started using OBJECT_NAME, OBJECT_SCHEMA_NAME and OBJECT_ID functions; unfortunately I really could have used OBJECT_TYPE but it doesn’t exist (yet?). So for now I wrote one of my own. ...(more)

SQL SERVER – Find First Non-Numeric Character from String - It is fun when you have to deal with simple problems and there are no out of the box solution. I am sure there are many cases when we needed the first non-numeric character from the string but there is no function available to identify that right away. Here is the quick script I wrote down using PATINDEX. The function PATINDEX exists for quite a long time in SQL Server but I hardly see it being used. Well, at least I use it and I am comfortable using it. Here is a simple script which I use when I have to identify first non-numeric character....(more)

Blogs : Virtualization

Windows 8 Hyper-V Support - I installed Windows 8 shortly it was released and I've been running it for almost two months now. I'm still in a learning mode to teach old dog new tricks. I have a few gripes here and there, such as the need for two screens (start screen and desktop), the absence of the start button and its search, the quest for dumbing down the OS, and the fact that my Quickbooks PDF converter doesn't work anymore. ...(more)