A Look Back at SQL Server 2012

 

 

SQL Server 2012 extended support ends July 12, 2022 – no more patches of any kind. I hope this isn’t news to you, and you’re already planning a migration. Before I post about a migration plan, though, I’d like to take a look back at SQL Server 2012.

 

What were some new features in SQL Server 2012, and how did they fare over time?

 

Administrative Features in SQL Server 2012

  • AlwaysOn Availability Groups:

 

This feature is an enhancement of database mirroring. The feature overcame the restriction in mirroring where databases were mirrored individually and did not necessarily fail over together. With an Availability Group, all databases needed for an application or set of applications can be put into the same container or Availability Group and those databases failover together. This is not possible with mirroring.

 

The number of readable replicas has increased as newer versions of SQL Server became available. We also got a feature called read only routing.

 

There were good enhancements in Windows failover clustering since Windows Server 2012 that make using Availability Groups a good option for HA and DR.

 

  • User-Defined Server Roles:

Prior to SQL Server 2012, there were only the fixed server roles and no way to create a custom set of roles at the server level. With this feature a data professional can create a custom role and add a login to that role to grant bulk operation permissions, and nothing else. Or, a ReadOnly role can be created at the server level, allowing members of the role to SELECT data from any database, including new ones that were added later. The feature is a time saver when it comes to permissions management.

 

  • Enhanced PowerShell Support

 

Microsoft started pushing PowerShell as a valid option before SQL Server 2012, but in this version of SQL Server, a lot more cmdlets were available for SQL Server. This is huge as it led to a massive rise in using PowerShell to manage SQL Server. Data professionals can not only manage SQL Server but even to create tables, insert, update, delete data via PoSH, call a stored procedure or execute a string of SQL.  This leads to so much more automation inside a single platform.  There is the native SQLServer module and also the DBATools module from DBATools.io.

 

  • Distributed Replay

This feature was meant to build on the ability of SQL Server Profiler to replay a workload for testing applications. It was billed as a method to replay workloads from multiple computers and provide a better simulation of the real world. As I look back on the description of this feature, I thought to myself, “But I can do A/B testing using Query Store, and if I want to find functional and performance compatibility issues between versions, I can use the Database Migration Assistant for that.”

The Microsoft page for Distributed Replay indicates that the feature is not available in SQL Server 2022.

 

 

Developer Features in SQL Server 2012

  • Columnstore Indexes:

 

Using this feature, data is stored in a compressed column index, greatly reducing I/O and CPU costs for queries.  Initially tables that had a columnstore index applied could not be updated while the columnstore index was present. Also, these indexes were only non-clustered. Later, In SQL Server 2014, tables with columnstore indexes could be updated while the index was still present and clustered columnstore indexes were available.

 

  • Big Data Support

 

Microsoft built an odbc driver for SQL Server that would use Linux and made connectors for Hadoop, a popular NoSQL platform. The Polybase feature came into existence some time later and involves the use of External Tables.

 

  • New T-SQL Enhancements:

Pagination

Using ORDER BY and OFFSET FETCH NEXT ONLY to display a page of data from SQL Server on a web page.

 

Error Handling with THROW–

Throw is a simpler way to raise an error in SQL Server code, and it offers things that RAISEERROR doesn’t. Check it out here.

 

BEGIN TRY

----do some T-SQL

 
END TRY

BEGIN CATCH  

THROW

END CATCH

 

Sequence Objects:

 

Sequence numbers are similar to the IDENTITY property but are not bound to a particular table. They also allow an application to get a next value BEFORE interacting with a table. This can be useful versus dealing with needing to get an IDENTITY value as part of application work.

 

Here is a list of other useful T-SQL that was introduced in SQL Server 2012. I encourage you to do some research on these helpful enhancements.

 

  1. CHOOSE (Transact-SQL)
  2. IIF (Transact-SQL)
  3. PARSE (Transact-SQL)
  4. TRY_PARSE (Transact-SQL)
  5. TRY_CONVERT (Transact-SQL)
  6. DATEFROMPARTS Function
  7. TIMEFROMPARTS Function
  8. DATETIMEFROMPARTS Function
  9. EMONTH Function
  10. FORMAT (Transact-SQL)
  11. CONCAT (Transact-SQL)
  12. First_Value Function
  13. Last_Value Function

 

Next Steps To Take

  1. Pick a feature above that maybe you’ve never heard of or you’re unfamiliar with and learn it well – just be sure to skip Distributed Replay since it didn’t fare well!
  2. Look at my PowerShell category to get started with PowerShell.
  3. is post, send me an email at [email protected], message me on Twitter or LinkedIn.
>