Press "Enter" to skip to content

Curated SQL Posts

Git Support In SQL Source Control

Published 2015-12-22 by Kevin Feasel

Mickey Stuewe looks at Git support in Red Gate’s SQL Source Control tool:

At the beginning of October 2015, everything changed. That was when Redgate announced SQL Source Control users could now push to and pull from remote Git repositories.

And not just from within SQL Source Control – from within SQL Server Management Studio (SSMS).

Your developers are probably using Git, so you should too.  If your developers are using Mercurial, I applaud them.  If they’re using SVN, CVS, TFVC, Vault, or anything else (Visual SourceSafe?), flip a coin and decide to use Git/Mercurial or the generally accepted tool…

Comments closed

WMF 5 RTM

Published 2015-12-21 by Kevin Feasel

Windows Management Framework 5.0 is available, says Max Trinidad:

Finally! The Windows Management Framework version 5.0 RTM is available for download for all down level Operating systems: Windows 7, Windows 8.1, Windows Servers 2008 R2, Windows Server 2012, and Windows Servers 2012 R2.

There are several interesting features here.  My favorite one is “Just Enough Administration (JEA)”; after all, who wants too much or too little administration?

Comments closed

Availability Groups Over Mirroring

Published 2015-12-21 by Kevin Feasel

Kendra Little explains why Always-On Availability Groups are better than database mirroring:

SQL Server Availability Groups are growing up. SQL Server 2016 adds more features and improvements, and these include options to run SQL Server in different domains, or without a domain.

That brings Availability Groups closer to feature parity with Database Mirroring in SQL Server 2016.

The recent improvements caused me to think about the ways Availability Groups are better than Database Mirroring that aren’t listed as “big features”. (By “big features” I mean having more than one replica, having multiple databases fail over in a group, having multiple failover partners in SQL Server 2016, readable secondaries, having a basic load balancing concept in SQL Server 2016.) I think there’s a few.

Thinking about this for a small business with just enough technical expertise to get by but no dedicated DBA, mirroring seemed like a better choice because there were fewer moving parts.  With 2016 and AG bugfixes, that might change the calculus.

Comments closed

Using Extended Events For Login Tracking

Published 2015-12-21 by Kevin Feasel

Steve Jones shows us how to track logins with Extended Events:

I can select any number of fields for capture. You can see I’ve picked the client_hostname. I would also add the NT_Username and Username from the list. You could add more, but in this case, I’m more concerned with seeing who’s logged in.

I could add filters, but I choose not to. I click on Data Storage to determine where to store this data.

If you’re not already familiar with Extended Events, that grace period is slowly slipping away.  Profiler’s going away sometime, and it’ll be a rude shock for a lot of DBAs.  Don’t be one of those…

Comments closed

Scrubbing Data

Published 2015-12-21 by Kevin Feasel

Tom Norman has a series going on scrubbing data before moving it to lower environments.

Part 1:

Have you ever heard, “but it works on my machine”? Is this because of data perfection in Development and QA or having specific failure conditions? Can you think of all the data scenarios that accompany Production data? What about performance? Why did the application fail? What happens if I add this index?

Here are the reasons I believe you should get a scrubbed version of your production database into your Development, QA and UAT environments.

Part 2:

All of us have Production database servers and hopefully you also have additional database servers for Development, QA and UAT. Some IT shops will also have a Continuous Integration server and maybe other servers. If you only have Production servers this needs to be addressed and is outside the scope of this post. In the locations where I have worked, we also have a Scrub server. The question is, when a script executes, do you know what environment the query is executing in? Most scripts will not care what environment the script executes in but other scripts could cause damage in a Production environment. For example, if the script is removing email addresses so you don’t spam your clients with automated email messages, you would not want the script to execute in a Production environment.

So how do you make your database server environmentally aware?

 

The concept of a dedicated scrub server is interesting; it’s not something I’ve thought about before.  I’m looking forward to seeing the rest of the series.

Comments closed

Watch Those DAX Calculations

Published 2015-12-18 by Kevin Feasel

Chris Webb shows us a case in which Power BI Desktop’s DAX calculation might not be what you expect:

The difference here is that the Month Number and Month Name fields are both present – they have to be since the query has to sort by Month Number. In MDX the order of members on a hierarchy can be set inside the model; in a DAX query you can only sort using an ORDER BY clause and for that to work, the field you’re ordering by must be present in the query.

This kind of feels like an edge case, but if you run into it, it’s good to know that it’s not a bug.

Comments closed

Data Analysis With R: Tutorials

Published 2015-12-18 by Kevin Feasel

Revolution Analytics has a series of tutorials using SQL Server R Services:

You may have heard that R and the big-data RevoScaleR package have been integrated with with SQL Server 2016 as SQL Server R Services. If you’ve been wanting to try out R with SQL Server but haven’t been sure where to start, a new MSDN tutorial will take you through all the steps of creating a predictive model: from obtaining data for analysis, to building a statistical model, to creating a stored prodedure to make predictions from the model. To work through the tutorial, you’ll need a suitable Windows server on which to install the SQL Server 2016 Community Technology Preview, and make sure you have SQL Server R Services installed. You’ll also need a separate Windows machine (say a desktop or laptop) where you’ll install Revolution R Open and Revolution R Enterprise. Most of the computations will be happening in SQL Server, though, so this “data science client machine” doesn’t need to be as powerful.

The tutorial is made up of five lessons, which together should take you about 90 minutes to run though. If you run into problems, each lesson includes troubleshooting tips at the end.

SQL Server R Services has the potential to be a great tool.  The standard V1 warning obviously applies, but I’m excited.

Comments closed

Object Level Auto-Stats

Published 2015-12-18 by Kevin Feasel

Andy Mallon on auto-statistics:

I’m not going to say whether I think Auto Update Statistics should be on or off. Instead, I’m going to argue that there are definitely scenarios when you want to have this on AND there are scenarios where you want it turned off. Can you really have this both ways? Absolutely.

I’m going to say yes to auto-update unless you know the answer is no for an object.  But it’s nice to know that the fine-grained option is available.

Comments closed