SQL Server working areas

Below you find the various areas within SQL Server in which I work as a freelance specialist.

performance tuning

Chances are that in a SQL Server application at some point in time performance problems will arise. Usage and the amount of data increase, and queries that used to work fine suddenly take way too long or lead to timeout errors. Adding extra resources may be expensive, especially in the cloud or in case of per-core SQL Server licenses. The good news is that with some effort SQL Server performance can usually be improved significantly.

SQL Server performance is influenced by many factors. Performance tuning involves tasks as different as optimizing physical resources (memory, disk, processor), finding the most expensive queries, analyzing query plans, rewriting queries, add adequate indexes to tables, configuring server and database settings, and others.

Performance tuning typically takes 1 or 2 days, depending amongst other things on the complexity of the databases and queries, and on the extend to which the customer itself can implement and monitor modifications. Knowledge transfer is almost always a part of the work.

See also my page what to know about SQL Server performance.

training

From 1999 until 2008 I regularly delivered classroom courses as a Microsoft Certified Trainer (MCT) for Compu'Train. These courses where usually based on the Official Curriculum (MOC). Every now and then I deliver a tailor made course for a customer or a in-house SQL Server crash course. In the latter the subjects are determined on the spot by the participants, which can be for instance a group of developers from a software vendor. These usually have already quite some experience with SQL Server, but their knowledge often contains many omissions or is not very well founded in deep understanding. Training days like these are very intense and rewarding, both for the participants as for me.

In much of the work I do there is a training on the job component. I like to work with one or two people from the customer that closely monitor what I do. This is usually the case at performance tuning or troubleshooting work, but also for instance when creating a datamodel or installing a server. I always encourage knowledge transfer, and my experience in many classroom or on the job situations is that almost everybody is motivated and eager to learn.

database design and implementation

Relational modelling is a pleasure for every database specialist. It involves getting at a normalized datamodel and creating the objects in a SQL Server database. The aim is to come up with a structure of tables and constraints that allows the data to be stored without redundancy or unwanted dependencies, and in which data integrity is maximally enforced. Performance can also be a concern here, although usually according to the principle: a well structured database is a well performing database.

In numurous contracts data modelling and the implementation of a database structure were part of the work. Also I delivered many courses on data modelling and the implementation of SQL Server.

T-SQL programming

Transact-SQL is SQL Server's server side programming language. It is a comprehensive, powerfull language that can be used to code the business logic of a database application. Also it is used a lot during data migrations and sometimes ETL. T-SQL comprises the declarative SQL language, as well as Microsoft's extensions, e.g. procedural elements and variables. T-SQL contains a large number of predefined procedures and functions. I have been working with this language during many contracts, including some big ones, since 1999. Before that I worked for some time with Oracle's pendant PL/SQL. T-SQL also contains an advanced development environment: SQL Server Management Studio.

A good T-SQL developer is primarly capable to write complex declarative (set based) SQL statements. He also knows when to add procedural constructions, and has a thorough knowledge of the primitives of the language. A good programmer in whatever language works orderly and systematic and is very able to think mathematically. Programming looks easy, and the first 80% often is easy... My academic study Applied Logic is not wasted on the job of programming SQL Server.

database administration & troubleshooting

Regularly I do all sorts of DBA activities, like installing and configuring, upgrading, the creation of backup and other maintenance routines, database security, and data conversion. Backup and maintenance may be simple involving one or two maintenance plans, or may quite complex using dedicated T-SQL scripts. Backup may for instance involve backup encryption or backup to Microsoft Azure BlobStorage. In some cases I only do the planning of SQL Server implementations, migrations or consolidations, possibly including the selection of SQL Server editions and licences.

Troubleshooting unexpected situations also occurs, from suspect databases, replication suddenly stopping, or the SQL Server process that won't start anymore. Luckily these are exceptions and often it involves studying the manuals or other resources. When I have to deal with these sorts of situations I know there is a big chance that eventually I will be able to fix it.

scalability and high availability

Important aspects of a SQL Server implementation are its scalability and high availability. Solutions for scalability can be divided in scaling up (increase the capacity of a server) and scaling out (division of work over multiple servers). Scaling out can done by replication, by the separation of transaction processing and reporting, by log shipping and also by Availability Groups or mirroring, possibly in combination with database snapshots.

It is important to be conservative with scaling out. It always introduces complexity and therefor I recommend to first investigate whether scaling up also suffices. If not, it is iportant to keep scaling out as simple as possible. So rather choose for transactional than merge replication, rather an Availability Group with readable secondary, or log shipping than complex ETL routines. SQL Server contains many features. Choosing the right one is crucial.

High availability can be realized for instance by failover clustering or (Basic) Availability Groups or database mirroring. With all these techniques I have at least some hands on experience.

business intelligence

On top of the relational database engine Microsoft SQL Server contains tools for ETL, multidimensional databases and reporting.

SQL Server Analysis Services (SSAS) is the component for managing multidimensional OLAP databases. These are both logically and physically optimized for querying and analysis. Where SQL is the query language for the relational databases, the multidimensional model has a query language of its own: MDX.

In a number (bigger and smaller) contracts I worked with Analysis Services and MDX. Also I used Excel pivot tables for the creation of slice and dice functionality, whether or not as front end for Analysis Services.

When reporting is separated from transaction processing, data should be regularly loaded. SQL Server Integration Services, Microsoft's ETL tool, is outside my expertise. I do have a lot of experience though with using Transact-SQL for ETL.

SQL Server Reporting Services (SSRS) is Microsoft's tool for web-based reporting. In the past I did various contracts in which I created reports with SSRS. I also delivered classroom courses about the product. Currently I am able to give high level advice on the use of SSRS, but creating reports is outside my scope.

Last modified: 18 June 2021