SQL Server 2012 – Best New Features for Small Business

Standard

I recently read through Introducing Microsoft SQL Server 2012, the book published by Microsoft Press. It’s a rather large book, and going through it I saw a lot of terrific features for medium and large enterprises, especially from a Business Intelligence point of view. However, I also pinpointed some features that I feel will be very useful for small businesses, so I wanted to give them a bit of explanation here. This is not going to be a series, just a single blog entry.

Multisubnet Failover Cluster

SQL Server 2012 features much better failover options than SQL Server 2008. My favourite, Multisubnet Failover Cluster, simply wasn’t possible in SQL Server 2008 because a failover server had to be on the same subnet. In SQL Server 2012, failover servers can be on different subnets. That means no VPN or VLAN is required between servers in different locations or network segments, which also frees up the possibility of putting your SQL failover server in a remote datacentre, whether it’s a colocation, virtual private server, dedicated server, or even a cloud server.

Support for Windows Server Core

SQL Server 2008 had to be deployed on a full-blown Windows Server instance. That’s no longer the case. SQL Server 2012 can be deployed on Windows Server core 2008 or 2012. This frees up additional resources for SQL Server, reduces the attack surface of Windows Server (therefore providing better security), and also reduces the number of patches that need to be deployed to the server overall. It’s a big win, but be sure to look at all of the things that are not supported when deploying on Windows Server Core. The list isn’t that bad, but you should be aware of the limitations.

Database Recovery Advisor

Backing up and/or restoring a database is now a much more visual experience in SQL Server 2012, and in a good way. The Database Recovery Advisor now gives you a visual representation of what point in time you would like to take a backup from (assuming transaction logs are available), and the same interface is provided for restoring backups. So now you can easily take a snapshot of a specific point in time, and restore back to that same point. Having a great deal of experience with this procedure, I assure you it’s a massive improvement over the current method.

Audit Supported on All Versions

SQL Server 2008 introduced the Server Audit Specification, and Database Audit Specification objects. These specifications were used widely for auditing and compliance requirements, but many users were not satisfied because these features were only available in premium versions of SQL Server. Many users had to rever to using SQL Trace instead, which brought about challenges. The Server Audit Specification and Database Audit Specification objects are now included in all versions of SQL Server 2012, and SQL Trace will likely be retired in the next version of SQL Server.

Contained Database Authentication

SQL Server 2012 now brings the ability to create users authenticated for only a single database, and transferring the database to another server also brings the user setup with it. That means greater portability for databases (important when moving a database to a more powerful/its own server), easier configuration of failover clusters, and just easier administration overall.

Those are the insights I wanted to share with you regarding SQL Server 2012 in a small business. Any other enhancements have been made around Business Intelligence capabilities which, while very interesting, aren’t critical for the day-to-day operations of a small business. Plus, if you’re doing homegrown BI, chances are you either specialize in such functions, or you’re not a small business after all.

Finally Taking SQL Seriously

Standard

On Friday, one of my co-workers and I decided to hash out a better way for us to find out that customers had added a reply to a help desk ticket. The idea was to poll the database for new comments, but only to have the script notify us if it was new within the last 5 minutes.

Well, that proved to be (at first) something I didn’t know how to do. Thankfully, with a bit of time, research, and trial & error, I was able to put together the code required. The result?

SELECT [SUBJECT],[INCIDENT],[CUST_ID],[COMMENTS] FROM [IncidentSQL].[dbo].[Incident] WHERE ([NEW_DTE] BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()) AND (STATUS_EXT = ‘NFB’);

We select just the four fields of information we need, and the first WHERE statement subtracts 5 minutes from the current date/time, and the AND statement makes sure we only find help desk tickets that have had new comments added in the last 5 minutes, and are currently set to the current statusĀ only.

I felt it was quite the feat for someone who hasn’t done any substantial SQL scripting since 2003, and felt good about seeing the results work. Next, we have to figure out how to email the results to the tech support mailbox without adding a ton of complexity.