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.