Chapter 6:​ Migrating Data and Code

Although it’s often easier to migrate your code and data as it exists on-premise, this can be a mistake. Since, you may have to pay for code that performs slowly and hold unnecessary data.

First, we look at migrating data. If you only migrate useful data, there will be less to do and store, resulting in lower costs. There’s a helpful look at archiving unneeded data (perhaps tables with names containing: archive, log, audit, old, tmp etc). It also makes sense to examine large tables to determine if they can perhaps be archived – while being aware of regulatory requirements to keep data. It’s important to get the business users involved her, since they will know the peculiarities of the data. The last section examines the importance of database best practices (e.g. have primary keys, index foreign keys etc). 

The second half of this chapter looks at migrating code. Tools like the DMA can be used to check if your code can be migrated successfully, and offers helpful solutions (and sometimes code) to fix issues. It’s noted that SQL Server’s Compatibility Level can be used to reduce some problems. There’s a useful section on finding issues with your pre-migrated database, e.g. long running queries, missing indexes, key lookups. 

Lastly, there’s a helpful section on baseline testing. It suggests using Microsoft’s Database Experimental Assistant [DEA] to evaluate source/target workload performance. It works by uploading profiler trace or XE trace. DEA will show differences between source/target. 

This chapter contains useful instruction on why you should aim to migrate only code and data that is useful. This should ease the migration process and hopefully result in a cheaper resource usage. There’s some useful SQL code for use in your analysis e.g. find table names containing ‘archive’.

Chapter 7:​ Team Success Factors

Although technical knowledge is important, many other factors are important for a successful project. Many of these factors are discussed here, and much of its content applies to any large IT project, not just migrations.

The chapter opens with a look at business value. If the project is worth doing, it will need a business justification. Getting this often requires support from the leadership. Various cloud benefits are discussed for this justification, including: faster time to deployment, pause/resume scale resources on demand (eg 1 hour high CPU), pay for what use, potential for lower costs, simplified admin, increased sec, improved HA/DR, and increased accessibility.

It's important to get business sponsorship, someone in executive management, involved in decision making (e.g. CTO, CIO). This person will provide a bridge between the team doing the work and the business stakeholders. They can help ensure the project stays on course, and offer a point of escalation. Often, projects like this have an impact on careers…

Next, the importance of supporting the team is discussed. This includes: early involvement, encouraging growth, and dealing with negative energy. I’m sure we’ve all been involved in projects where we feel concern in these areas.

The importance of good communication is examined, it’s important that staff have all the information they need, with clear priorities, to succeed. Where necessary, it’s important to train the team, some organizations rely on staff learning on the job, however this can create unnecessary stress – typically because training isn’t given a high priority. 

Similarly, the importance of asking for help is noted. with any new topic, there will be areas that may seem difficult and complex. Talking to Microsoft or getting external help is discussed.

The chapter ends on a high, discussing celebrating wins along the ways and having fun – both of which affect morale, and the ease of working. This is a fine chapter, but I do wonder if it is necessary in a book primarily about migrating to the cloud.

Chapter 8:​ Security, Privacy, and Compliance with the Law

The importance of security has grown in recent times, with the public demanding greater data protection. This has been encoded in various laws (e.g. the EU’s GDPR), with harsh penalties. The chapter covers various related aspects, including: security (who can access system and what they can do), privacy (legal and regulatory, prevent access), and compliance (practices to uphold law, often via yearly audits). This chapter is largely about Azure SQL security. 

The chapter opens with a look at recent changes, where the 2020 covid pandemic has driven the accelerated the move to the cloud. With more working from home there are more potential targets for hackers. There has been a significant increase in the various types of attacks, including: DDOS, man-in-the-middle, and SQL And XML injection. There has been an 800% increase in recent ransomware attacks.

The authors suggest, especially for those new to cybersecurity, the use of a popular framework (3 are recommended here). This helps give guidance, standards, and best practices, in an area that can quickly become overwhelming.

Next, the chapter discusses a cornerstone of security – defend in depth. The more security measures you have in place, the more difficult it is for a hacker to penetrate your systems. Topics covered include: Access Control, Network Address Translation, Azure Virtual Networking, and Access to Services. It should be noted that while the discussions given here are important, they are largely introductory, you will need to get some experts involved to secure your systems. 

The chapter continues with a look at Virtual Networks, firewalls, Azure private endpoints, Azure Service endpoints, Authentication, Azure SQL Database firewall, Dynamic Data Masking, and encryption. Each item is discussed in relation to how it works and the security it provides. 

The chapter ends with a look at Advanced Data Security. This provides more layers of defence, but has an additional cost, these include:

• Advanced Threat Detection – lots of useful examples: brute force SQL attacks, access from harmful apps, access for unusual location, and SQL injection
• Vulnerability Assessment – scans databases for wide variety of security issues, misconfigurations, unsecured data, excessive permissions etc

This chapter covered a wide range of topics related to security. I should make it clear again, you will need various security experts engaged in your projects to secure your systems. No one can be an expert at everything (or perhaps even one thing!). 

I suspect this chapter will appear detailed to many readers (architects, DBAs, PMs etc), I’m not sure how much would be truly understandable to many.

Chapter 9:​ Documenting Data Sources and Metadata in a Data Dictionary

As systems increase in size and have more complex data, analysis can become increasingly difficult. Having a data dictionary can help. It records the type of data, its meaning, and how and where it’s used. This can become increasingly important for data-driven systems. Azure Data Catalog and its successor Azure Purview can be used to build a data dictionary, but these are currently limited.

There’s a useful section on creating your data dictionary, exploring various methods, including: using extended properties, data classification and labels, and inputs from system views and INFORMATION_SCHEMA. Much of this work is manual, but the authors do supply some useful code to help automate and speed up the process. The next stage looks at higher level linkages that you might want to record, including: linked servers, object dependencies, and data lineage. The idea is to record various dependences between databases and applications. It should be noted again that the involvement of users is important in deciphering the meaning of the underlying data fields. There’s a quick look at Azure Data Catalog, which the authors note should not be used for any new work, since it’s superseded by Azure Purview.

On a personal note, this chapter made me smile because it contains some of my original work, relating to how to identify columns that have the same names but different data types in different tables – this can result in implicit data conversion, and sometimes means an index cannot be used.

This chapter contains some useful insight into the importance of a data dictionary, and the various ways it can be built. That said, I do wonder if it deserves a place in a book about migration.