On 12 April 2011 it was Yuri’s Night — the night we space fans celebrate Yuri Gagarin’s 1961 history-setting flight into space. In 2011 we were celebrating 50 years of manned spaceflight. On that same day in 2011, we reached the end of support for SQL Server 2005 SP4. On 12 April 2016 we will reach the end of extended support for SQL Server 2005. That means no more hotfixes, no help from Microsoft and no love for your data still living in SQL 2005 databases.
I’m hoping your organization is already on its way to upgrading and migrating data and applications to newer versions of SQL Server. SQL Server 2016 is already being used in production by early access customers. No matter which version you will be migrating to, I want to share with you some of the features and perks you’ll have available to you now that you are moving away from a dead version. Of course there are hundreds of enhancements that have happened since 2005, but today I’m focusing on those that a data architect would want to use in a database design for enhanced performance, security and data quality.
If you are designing for data warehouse type solutions, this is the closest thing we have for a "turbo switch" for SQL Server. Columnstore Indexes achieve high compression rates since they store columns together instead of storing rows together. They also support much faster query performance for batch and aggregation queries. They typically achieve 10x performance increases, sometimes even more. This feature was introduced in SQL Server 2012, but you’ll want the advances to this feature that came with SQL Server 2014.
SEQUENCEs have been around in other DBMSs for a while, but were introduced in SQL Server 2012. These special objects work much like IDENTITY columns, but offer more flexibility and use cases. The main feature is that you can grab a sequence (or a bunch of them) before you insert a row. Many developers use GUIDs for similar reasons, but GUIDs are much longer and therefore had performance downsides. SEQUENCEs are integer types.
New Data Types
So many new data types have been introduced since SQL Server 2005, but the ones that really stand out for me are DATE, TIME, DATETIMEOFFSET, the geospatial types, and the deprecation of timestamp.
It wasn’t until SQL Server 2008 that we had access to data types that comprised only the DATE or TIME portion of a point in time. So we had to do all kinds of conversions just to strip out unwanted data (00:00:00). We also had to make room to store that unwanted precision. Storing millions of rows of unneeded zeros hurts performance, both operationally and for backup and recovery.
SQL Server 2008 also introduced DATETIMEOFFSET, which allows us to track data in context of its time zone. If you remember the days when meeting invites did not include this important piece of information, you’ll know why this is important.
The spatial data types GEOGRAPHY and GEOMETRY and have added a new and feature-rich way of tracking places, their geometry plus special features that make it much easier to answer questions like "which is the closest" or "is this address located in this neighbourhood". If your data story includes location points, you’ll want to use these.
SQL Server was always an oddball when it came to the data type TIMESTAMP. In other DBMSs, this data type was one that included date and time, to a very large precision. In SQL Server, TIMESTAMP is a type of row version identifier that has nothing to do with TIME. So data architects migrating from other DBMSs were often bitten when they used the wrong data type. Microsoft announced in 2008 that it was depreciating TIMESTAMP and recommending the use of ROWVERSION, which is similar (but not the same) in functionality.
SQL Server 2016 currently includes support of Always Encrypted, a feature that does just that: it support the encryption of data from application to database and back, so that it is better protected than solutions that encrypt data once it is written to the database. I’m always reminding you that keeping your CIO out of jail is part of your job description, right?
As our data gets bigger and bigger, the size of our databases is growing as well. That means that performance takes a hit. Developers want us to take shortcuts on data quality to improve performance because size matters. One of the ways to help manage data volumes is to move "cold" data to other storage locations. Starting in SQL Server 2016, we can stretch a database to Azure, which means that data that isn’t accessed as often can be stored in the cloud and retrieved when needed. This allows our hot data to be local and fast, while the cooler data is more economical to store and still there and your application doesn’t even have to manage this difference.
In SQL Server 2016 we are getting support for JSON processing. This isn’t the same as a JSON data type like we have with XML, but a set of import and export features for providing relational data as JSON documents and brining JSON data into SQL Server. Now you won’t have to manage all those curly brackets on your own.
One Last Thing…
As vendors withdraw support for their products, third party tool makers do so as well. If you are supporting older, out of support versions of databases, it’s likely that your data modeling, data quality and data integration tools are also dropping support for these solutions. You’ll be left supporting database systems without vendor support and without professional enterprise class modeling and design tools. I know how hard it is to keep track of databases that my tools can’t connect with. Don’t let sticking with an old version be the end of data modeling support for that data.
If you like geeking out about space and data types, you might want to check out my 1 April 2014 post on a new data type.
Show Your Data Some Love
These are just a tiny number of the types of features that will be available to you when you upgrade to modern versions of SQL Server. The advent of security, data quality and performance features are leaving your old solutions behind, putting your data at risk and leaving your customer data feeling unloved. There’s a data space race going on. Don’t live your company using old technology to manage data. Go fix that!
My general advice on this question (and I get it a lot) focus more on fit to your organization and technical environment than on specific features. I should disclose that we partner with most the major data modeling tool vendors, but we don’t make any money from the sales of such tools. We aren’t a reseller and don’t get any referral fees. The partner programs I’m part of with these vendors is closer to an MVP or Ace program.
Target DBMS and Development Tool Support.
It depends on what your target DBMSs and data stores are, of course. While I am a huge fan of modeling for the knowledge, to document the business, and to be able to communicate requirements, ultimately to get the full value of modeling we need to ensure that our modeling products are directly able to support development and implementation activities as well as all the requirement gathering.
I see weaker returns on the value of modeling when the chosen modeling tool cannot be easily consumed by developers in their tools, or when the tool cannot forward and reverse engineer databases. This also include XML files, non-relational DBs and even the most common database in the world, Excel.
I consider this one factor the most important of all in the post.
Modeling Not Just Drawing
Every obstacle between the data models and another person’s ability to consume them is an obstacle for the models being integrated into the full development and support lifecycle. There’s very little chance of Model Driven Development being successful if all that wonderful data model goodness has to be retyped (and reinterpreted) into someone else’s process or products.
A data modeling tool needs to support the highly-iterative nature of data modeling. Drawing and database diagramming tools rarely have the ability to compare and resolve differences between models and databases. I do these types of compares many times a day, across many models and databases.
Data modeling tools also allow data architects to design and record important information beyond what might appear in an ERD or DDL. These features include security, encryption, capacity planning, data privacy and a slew of other critical data requirements. In shops where a diagramming tool such as Visio is used for data modeling, these important meta data items are typically buried in a spreadsheet or document. They can’t be easily searched or reported on, nor easily shared across projects.
Vendor Tech Support
When you buy a data modeling tool you aren’t just buying a product – you are investing in a vendor. Log a few tickets during evaluation. Learn how well support works. Remember, the vendor will never treat you better than when you were considering a purchase. If support is slow or non-existent it’s not going to get better after you buy. Don’t use your sales person as the support person. They may be great but they might not be your sales person tomorrow. You are evaluating the support process and system, not one person.
If the vendor says that tech support isn’t available to evaluators, ask them why not and tell them you need to evaluate them as well as their product.
The technical architecture landscape is changing faster than most vendors can respond. The good news is that often that means it’s changing faster than your organization can change as well. However, you need to review the history of releases of the product to assess the responsiveness of the vendor to changes in the industry. Sure, past performance is not a guaranteed indicator of future behaviour. It is something to consider, though.
Ask to see public roadmaps or presentations about products/features in development. Vendors can’t always share a lot of details but generally they have information they can share either at a high level or features that are currently in beta testing.
I don’t know about you, but my work happens in many locations and across many devices. As much as I’d like to buy a separate license for every desktop, laptop and tablet I own, I’m not going to do that. I do most of my modeling on a desktop with three screens because it makes me more productive. However, when I am in a meeting I want to use my laptop or tablet. If the only licensing scheme available is via device or MAC address I either have to buy multiple licenses or remote into my desktop to do the work. Neither one reflects how modelers really work. Licensing options such as floating/concurrent or user-based do.
If common tasks take hours instead of seconds the cost of using the tool may exceed the value it can deliver no matter how pretty your models are. If publishing models takes more than a few minutes you won’t publish (share) as often. You’ll want to evaluate the full lifecycle of modeling, including forward and reverse engineering, printing, publishing, sharing, exporting, importing…everything a professional data modeler needs to do to get his job done.
A team of data modelers (even a team of one + co-workers like developers and DBAs) must have real version control for models. That means not at the file level but at the object level within the model. A tiny change in one part of the model can ripple throughout the model completely unseen on the current diagram. The ability to compare the impact of a change to previous versions of the model is critical. Because data models are typically persisted in a proprietary format third party version control tools like Subversion can only detect changes at the full file level, not at the object level.
A drawing tool isn’t going to have these features.
If the tool doesn’t fit well within a corporate IT environment it’s going to be difficult to get support from your own IT resources. Support for the types of requirements you’d have for a business application is key. Active Directory, password complexity requirements, installation permissions required, target DBMSs supported, data file location configuration are just some of the examples of things to evaluate.
Your data models are production data for you, the modeler. You want production level support from your company’s IT resources. If the product isn’t enterprise ready, you’re going to have a tough time getting that support.
A vendor that participates and supports professionals in the field is one that is demonstrating how much they want to see users of their products succeed. Do they have product evangelists who help users succeed by blogging, creating videos, speaking at events, etc.?
Does the vendor sponsor user groups? Conferences you attend? Do they have a user group they fund? Do they they have a form product advisory group that helps product management understand the needs of data modelers in the field?
A vendor that gives back to the community is more likely to deliver the product features you need, plus help you be successful at data modeling.
All of the above features come at a cost, as do all enterprise class tools. But organizations are going to pay those costs whether or not they use Excel, Visio or FreeDrawingTool to manage their data architectures or a real data modeling tool. In my experience the real costs using these “cheap workarounds” greatly exceeds the costs of using a real enterprise data modeling tool due to lost productivity, data modeling errors, lack of reuse and wasted business user time in recreating the same data models over and over again.
Data modeling is more than diagramming tables. ERDs show about 10% of the information in a professional data model. You need tools that support your environment and data modeling goals. It’s less expensive, faster to model and your models will continue to deliver benefits for years and decades afterwards.
Subscribe via E-mail
- September 2016
- August 2016
- June 2016
- May 2016
- April 2016
- March 2016
- February 2016
- January 2016
- December 2015
- November 2015
- September 2015
- July 2015
- June 2015
- May 2015
- April 2015
- March 2015
- February 2015
- January 2015
- December 2014
- November 2014
- October 2014
- August 2014
- July 2014
- June 2014
- May 2014
- April 2014
- March 2014
- February 2014
- January 2014
- December 2013
- November 2013
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- September 2010
- August 2010
- July 2010
- February 2009