How to Improve Data Quality

Is he 98 or 298? It all depends on the data type ....

Designing a new database is exciting; new ways of looking at data are being created. But there is often frustration mixed in with the excitement as there is a certain amount of groundwork to get the project off the ground.

This includes figuring out what information needs to be stored. It is a process of going through the existing business processes, envisioning the new system, and deciding what is or is not needed. All now with an eye towards GDPR.

And making the right decisions about your data will help make the new system a success.

Having decided what data needs to be stored, there are more questions. What range of values do you expect to hold? Will you always know the value when a new record is entered? Could the value ever be negative? What is the largest value the file might hold? It is at this point that the frustration invariably turns to annoyance. "It doesn’t matter", comes back the reply. "We will think about that later" is another favourite. "After all, we’re not short of disk space, so what’s the problem?"

Yet each one of those unmade decisions is an opportunity to improve the quality of the data being held. And potentially the possibility of a bug. These decisions are not about running out of disk space, but about making sure the data you hold is the data you intended. The data type for each field is the most basic and most valuable constraint in a database. It is what separates relational databases from other ways of storing data, such as spreadsheets.

The data that can be entered into a particular field is constrained by a number of things:

• The data type. This ensures that, for example, a date value cannot be entered into a field designed to hold an integer.
• Primary and foreign keys. As well as providing relationships between tables, they also check data as it is being entered. A value entered into a foreign key field must match the value in another table’s primary key.
• NULL or NOT NULL. Fields that allow NULLs risk introducing hard-to-debug issues when data is added to the system. It is often necessary to allow NULLs, because the information may not always be available when records are added. However, fields that allow NULL can produce query results that are not the expected results.
• Allowable ranges. Whilst some data types automatically limit the range that can be added, others do not. For example, a SQL Server tinyint cannot take values greater than 255, or values that are negative. This might make a tinyint a good data type for a field holding age values. A SQL Server smallint, on the other hand, may be negative or positive, and allows values between -32,768 and +32,767, making is a poor choice for an age field, as incorrect data could easily be entered.  

There are very many more examples of how data types and constraints help keep data quality high. Whilst I fully admit that the age example is not ideal (it would be far better to hold date of birth rather than age), it is easily understood and illustrates the point.

Of course, things can be changed in a database system. You can add constraints later, or change constraints you got wrong. But everything comes at a price. And sometimes that price is reliability because every change in a system has consequences - sometimes unexpected ones.

So if you are getting impatient with never ending questions about your data, think of it as an investment in keeping the quality of your data high for years to come.

Encryption and the SQL South West User Group

It is a long and beautiful drive from Reading to Exeter. As the miles crank up, the scenery becomes more lovely. The motorways signs to Glastonbury and Bodmin Moor are a reminder that I really should spend less time in front of my PC, and more time in my hiking boots. But as Exeter cathedral appears in the distance, my thoughts turn to encryption, and SQL Server. I’m here to talk to the SQL South West SQL Server User Group about Always Encrypted, the new encryption feature in SQL Server 2016.

The group is attentive and interested. The discussion soon turns to key management, an important issue when considering encrypting data. No one was sleep walking through the presentation: the young stars of tomorrow had already realized that the inside threat of losing keys was at least as great as the outside threat of being hacked. The balance between security and availability is an issue that faces anyone with an interest in data security. Yet the unrelenting wave after wave of cybercrime makes it hard to ignore the message that the only safe data, is encrypted data. Particularly where personal data is concerned.

So we munched pizza as we talked about backing up encryption keys, azure key vault, and the way the data world is changing.

After eating far too much anchovy pizza we then listened to Rob Sewell’s presentation on PowerShell, and in particular the new SQL PowerShell module. As always, I learnt much, and my head was spinning with ideas as I drove back through Taunton, headed to Bristol, and then back on terra firma as roadworks on the M4 reared their cone-like head. It was a late night, but worth it. You are a lovely lot in Exeter, and I’ll be back. And next time I’ll have a lighter lunch so I can manage a bit more pizza!

Writing Microsoft Official Curriculum for SQL Server 2016

SQL Server 2016 was a big new release, with a host of eagerly awaited new features. And like many people, we installed the various beta releases. But for this release we had a bit more of an incentive to try out all the features, including the corner cases, and generally put SQL Server through its paces. The reason? I was on the team writing the Microsoft Official Curriculum to accompany the new release. What a fun project!

Now I don’t know about you, but I’m always conscious of the massive development effort that goes into a new release. We know from the blogs and videos released by Microsoft that there are quite a few development teams, managers, and others working across the globe developing SQL Server. What I don’t think about quite so much, is all the other activity that is required. The marketing, the training courses, and the pile of technical documentation that is also needed.

Microsoft Official Curriculum is just a part of the training documentation needed, but it was still a big job, with a team of writers, a dozen or more courses, with each course containing a dozen or more modules.  There was a lot of researching, a lot of writing, a lot of testing, and more than a few late nights. The team came from different backgrounds including data warehousing, SQL Server development, report writing, and programming. There was a great team spirit, with everyone happy to help who were struggling with something. There was also pressure to get everything written on time – pressure that wasn’t always welcome when something wasn’t working. With pre-release software it isn’t always obvious whether it is a bug, whether you have missed something, or plain and simply have made a mistake.

I wrote a number of different courses including part of the Upgrade Your Skills to SQL Server 2016 course. This course is designed for people who already know SQL Server, and just want to learn about what’s new. It was a challenging course to write because features were still in development, and the documentation was incomplete. And just occasionally something would change after a lab exercise or demo had been written, but before it was tested. It didn’t happen very often, but it did happen, and it added a bit of not always welcome added spice.


The module I enjoyed working on the most was security. In our digital and cloud world, security has become a hot topic, with cybercrime showing no signs of abating. The new features in SQL Server 2016 are much needed, and will help a huge number of organizations to better protect their data.

5 Awesome Benefits of PowerShell

Jeffrey Snover, Microsoft. Inventor of PowerShell

If you thought that the command line had been confined to history, think again. There’s a not-so-new kid on the block that is getting a lot of attention. It’s called PowerShell, and was first released with Windows 7 and Windows Server 2008 R2. The latest release, version 5.0, will be included in Windows Server 2016 which is currently in technical preview.

PowerShell is a scripting language that is designed to automate server tasks. It can run interactively or in scripts, and is super useful for all sorts of things. It was a ground-up redesign, and has a refreshing elegance to it.

First, a few basics. PowerShell is based on .Net classes and is implemented using cmdlets. A cmdlet has a verb-noun syntax that is designed to be descriptive, and as far as is possible, intuitive. Examples of cmdlets are get-help and get-command.

Cmdlets are organized into modules, with each module containing cmdlets for a particular product. There is a SQL Server module, an Azure module, and an Active Directory module, plus many, many more. Modules are either loaded, or unloaded.

But why would you use PowerShell rather than the GUI? There are a number of awesome benefits:

1. A PowerShell script is testable. Once developed, it can be tested and signed off to say it does what it says on the tin.
2. A PowerShell script is repeatable. It may be quick to do something once using the GUI, but it is slow and error-prone to do it many times.
3. A PowerShell script can be saved. For tasks that are not done very often, or need to be done by different people, having a script ensures that the job gets done in the same way every time.
4. You can do more with PowerShell. Not everything is built into the GUI, so PowerShell lets you do more stuff than is pre-built into the GUI.
5. PowerShell is less exciting than the GUI. If you’ve got complex changes to put into production, PowerShell takes much of the risk out of the deployment. You can test the scripts multiple times, and in different situations. You can be sure that the script does exactly what is intended, without any variation. And even if it is run at 3am, it will do exactly what it was intended to do. So much less exciting than hoping it is all going to go OK.

As the move to the cloud is gaining momentum, and Microsoft Azure is increasing in popularity, PowerShell is proving its worth. It is ideal for working with Azure, enabling resources to be commissioned or removed easily.

 

But perhaps the most awesome aspect of PowerShell is its irrepressible inventor, Jeffrey Snover. With his bulging wardrobe of bright ties and impish grin, you would think he had just invented ice cream. Maybe he has – the Windows version of it anyway.

Designing for Data Protection

The rise in cybercrime cannot have escaped many people’s attention. The national news regularly includes stories about organizations that have been the victims of hacking. LinkedIn and TalkTalk are two recent high profile incidents, but there are many more. So prevalent is cybercrime that the Office for National Statistics now includes it in the crime statistics.

Smaller companies often believe that they are less vulnerable than their larger corporate siblings, but this is not the case. Smaller companies often have fewer resources, and are less well educated in the issues surrounding cybercrime. At the same time, many small companies keep personal data about customers and staff in database systems, which is exactly the sort of data that cybercrime is targeting.  According to the Federation of Small Businesses, over 40% of its members have been a victim of cybercrime in the last year, at a cost of £4,000 to each business.

So how can organizations protect personal or sensitive data?

As a first step, identify the sensitive and personal data that is being held in databases, either on-premises or in the cloud. Organizations have a responsibility to protect data that an individual considers personal, such as email addresses, date of birth, telephone numbers, etc. If you have personal data in more than one system, consider whether that data could be held in a single database, and then securely accessed from other systems when needed. It may be easier to increase the level of protection for one database, rather than ensure that multiple spreadsheets and local databases held on laptops are all secure.

Then consider whether all the sensitive data that is being held actually needs to be stored. Credit card information, for example, often should not be stored in a company database. Although customers may be asked to provide credit card details multiple times, this is small beer compared to the trauma of credit card data being compromised. Read one of the recent stories about hacking, and then look at the list of personal data that you hold. You may find that some of that data is not being used sufficiently for the risk it posts.

Passwords should never, ever, ever be stored in plaintext. They should always be stored using salted hashing. If you are storing passwords in plaintext please take steps to amend your systems. Now.

Security is a multi-layered problem, which means that you need to employ a multi-pronged approach. Ensure staff understand the importance of using strong passwords to secure workstation and servers, and that passwords are changed regularly. Personal data can and should be encrypted, ensuring that not even database administrators have access to sensitive data. Keeping database software up to date also means you have access to the latest encryption technologies.

Data protection is a big subject, but thinking defensively gives you a head start. If you think it could never happen to you, you probably don’t have the right mind-set. We design SQL Server databases, upgrade them, and migrate data to the cloud, all with data protection issues firmly in mind. If you are considering developing a database for your organization, or migrating an existing database to the cloud, contact us for a free 2-hour Data Protection Review. We can advise you about sensible steps you can take to protect sensitive data from hackers, as well as advising on the new security features included in SQL Server 21016.