Modernising Your SQL Server Database

Last year we ran a series of workshops dedicated to SQL Server and Azure SQL and they were some of our most successful to date. This is hardly surprising given many of our customers deploy their IT on the Microsoft stack. Database technology has made huge strides forward in recent years, partly driven by the greater complexity and quantity of data we’re handling and the need to extract greater value from it. In this blog, I’ve provided links to summaries of the newer features of SQL Server that have been provided by Microsoft to help you modernise your IT.

The critical nature of databases poses challenges for the individuals and teams responsible for their high availability, security and compliancy. Upgrades or migrations aren’t undertaken lightly and it’s not unusual for production databases to be years old. The adage ‘if it ain’t broke, don’t fix it?’ has some merit in the database world, but it has downsides too. Old editions of software are likely to be unsupported and less secure, and will almost certainly lack the features required for today’s systems modernisation projects.

Old database releases continue to provide excellent service in many IT departments so regular refreshes are not always a top priority. If your database isn’t keeping up with the demands of the business, regular upgrades will become a fact of life in order to get the latest technologies into the hands of your developers. Microsoft provides tools to assist with this and I’ll come to these shortly. I’ll also take a look at some third party products in a follow-up blog. First, a few pointers if you need to catch up with SQL Server 2017.

Getting to know Microsoft SQL Server 2017

In recent years, SQL Server has benefited from a number of new features to equip it for the new digital world which almost inescapably involves the Cloud, or should I say Cloud technologies.

Some of the new features in SQL Server 2017 are summarised here

For a comparison of the editions, see here

The Developer Edition has all the features of the Enterprise Edition and will help you get up to speed with the latest features. It can be used throughout the development phase of the project but not for production. Grey Matter can assist with licenses for production and would be happy to provide advice and budgetary pricing early in the project lifecycle.

You can download the Developer Edition here

So it looks as if an upgrade or migration might be on the horizon!

The ideal project is one that allows you to start from scratch with the ‘latest and greatest’ release of SQL Server 2017 but things are seldom that straightforward! Even if you are afforded this luxury, you’ll still need to plan ahead for change whether that involves an update, upgrade or migration to a different platform at some point in the future.

For now, let’s assume the need has arisen to modernise an existing database, maybe due to performance issues or perhaps to meet the demands of an expanding business. This is the upgrade or migration scenario that has the potential to trigger various degrees of panic in the IT department, depending on your state of preparedness!

Numerous things have to be taken into account such as identifying the edition required and the impact it will have on the applications to be supported both now and in the future. Then there’s the cost of the licenses and probably the hardware. Add to that the likelihood of an immediate skills shortfall if the incumbent database falls into the legacy category, and it’s hardly surprisingly many businesses choose to outsource or bring in consultants to help. Others are offloading the management of their databases either partially or fully by placing the workload in the Cloud where patches, security, DR and other management tasks are handled by the service provider. Azure provides an ideal platform for this and Grey Matter’s Services Team can assist with the migration project.  They can be reached at [email protected]

Updates

Keeping your database up to date with the latest security patches and updated features is increasingly important in the light of compliancy regulations such as GDPR but unlikely to help you fully modernise your IT in the case of an old database version. The Update Centre for Microsoft SQL Server provides links to the various Service Packs and updates going back as far as the 2000 Edition. The Update Centre is here

Upgrades

Upgrades are more involved but help is at hand in the form of the Data Migration Assistant (DMA) which will detect compatibility issues that might be encountered by the newer version of SQL Server or Azure SQL Database. In addition, DMA can help with performance and reliability improvements. The tool allows you to move schema, data and uncontained objects from source to target. The source can go back as far as SQL Server 2005 up to 2016 while supported targets span 2012 to 2016 and include Azure SQL Database. It shouldn’t be too long before SQL Server 2017 is added to the list.

It would be a mistake to assume every upgrade will be straightforward. DMA will help you identify issues classified as Breaking Changes, Behaviour Changes and Depracated Features, and make recommendations to solve any obstacles. This will help you scope the project and make informed decisions about what needs to be done.

Migration

Migrating from a different database platform altogether can be daunting unless you’re familiar with both the source and target environments. For migration purposes Microsoft provides a SQL Server Migration Assistant (SSMA) for most of the common data platforms used by businesses today. These include DB2, Oracle, Oracle MySQL, SAP ASE and Microsoft’s own Office database, Access. The target versions of SQL Server go back to 2008 up to 2017 on Windows / Linux (Preview) and Azure SQL Database. SSMA includes assisted support if further help is needed. Access migration is a fairly frequent requirement where businesses have outgrown their use of the Office application or used it for a prototype or proof-of-concept model before embarking on a full production system.

The Cloud can help

The Cloud reduces many of the management chores that weigh heavily on the datacenter. Moving your SQL Server database to Azure SQL Database is straightforward. You will need to create an empty SQL database in Azure and migrate your database into the empty one in Azure using the Data Migration Assistant (DMA). Azure provides many advantages as a deployment platform for SQL Server such as the ability to extend your data storage options. Stretch Database is one of the key features introduced in the earlier 2016 edition of SQL Server which allows you to stretch your data from on-premises to the Cloud. Stretch Database is ideal for large transactional systems. These typically hold a lot of cold data in a relatively small number of tables which can be stored more cost effectively in Azure. This feature isn’t suitable for all use cases and the Stretch Database Advisor will help you identify the databases and tables that can take advantage of the technology.

If you’d like to talk more about systems modernisation, the benefits of both on-premises and cloud-based SQL Server implementation, and how you’d migrate from your current database, please get in touch with Grey Matter’s technical experts: [email protected]