DBAs can control many aspects of SQL Server’s behavior with trace flags. In this article, Robert Sheldon explains how to enable SQL Server trace flags.… Read more
The SQL Server transaction log must be managed to keep a database running and performing well. In this article, Greg Larsen explains how to manage the transaction log size.… Read more
The transaction log file for a SQL Server database critical for for maintaining database integrity. In this article, Greg Larsen explains SQL Server transaction log architecture.… Read more
DBAs must make sure data is highly available, and there are many SQL Server high availability options. Pamela Mooney discusses those options in this article.… Read more
Understanding SQL Server security is a critical skill. Greg Larsen explains SQL Server authentication methods, logins, and database users in this article.… Read more
In this article of the series, Pamela Mooney explains the architecture of SQL Server under the hood, including some query anti-patterns to avoid.… Read more
Mastering TempDB is a critical skills for SQL Server DBAs. In this article, Monica Rathbun explains the basics of TempDB including configuration.… Read more
SQL Server tech interview questions must be well crafted to make sure the candidate actually knows the topic. In this article, Sergey Gigoyan provides his favorite questions for interviewing SQL Server developers.… Read more
There are a handful of options when backing up SQL Server databases. A DBA must understand the differences and come up with a plan that protects the organisation’s data. In this article, Pamela Mooney explains service level agreements, recovery models, and some strategies to ensure that the data can be restored quickly.… Read more
Securing data is not always easy to do, but it should be the top responsibility for database administrators. From protecting the physical servers to preventing copies of backups files from getting into the wrong hands, there is a lot to consider. In this article, Pamela Mooney covers what DBAs need to think about when securing their organisation’s data.… Read more
Tables that return the value of the data in the table at a particular point of time have been with us since the first relational database, but have always required special queries and constraints, and can be tricky to get right. System-versioned Temporal Tables, new in SQL Server 2016, make such tables behave like any other. How do you create one, or modify an existing table? How can you get an In-Memory Optimized OLTP table to be Temporal? Alex Grinberg shows how. … Read more
How do you go about transferring a disk-based workload to the respective memory-optimized design? How do you process memory-optimized tables? How important for performance are natively-compiled Stored Procedures? Artemakis Artemiou comes up with a step-by-step guide to implementing an in-memory OLTP solution.… Read more
When maintaining or refactoring an unfamiliar database, you'll need a fast way to uncover all sorts of facts about the database, its tables, columns keys and indexes. SQL Server's plethora of system catalog views, INFORMATION_SCHEMA views, and dynamic management views contain all the metadata you need, but it isn't always obvious which views are best to use for which sort of information. Many of us could do with a simple explanation, and who better to provide one than Rob Sheldon?… Read more
At last, SQL Server has caught up with other RDBMSs by providing a useful measure of JSON-support. It is a useful start, even though it is nothing like as comprehensive as the existing XML support. For many applications, what is provided will be sufficient. Robert Sheldon describes what is there and what isn't.… Read more
In-Memory OLTP, aka Hekaton, originally shipped with 2014, and although it certainly helped the performance of certain types of workload, it then had certain restrictions that impeded its widespread adoption.With SQL Server 2016, there is more support for In-Memory OLTP and a more seamless integration with SQL Server's Database Engine. It is time to consider whether In-Memory OLTP can help those pinch-points in your data throughput, explains Artemakis Artemiou.… Read more
Access-control within the database is important for the security of data, but it should be simple to implement. It is easy to become overwhelmed by the jargon of principals, securables, owners, schemas, roles, users and permissions, but beneath the apparent complexity, there is a schema-based system that, in combination with database roles and ownership-chaining, provides a relatively simple working solution.… Read more
Whereabouts in the application should the business logic of data-validation checks be made? The advantages of a layered approach to validation that includes database constraints, would seem to outweigh the disadvantages. William Sisson explains some interesting issues.… Read more
To access SQL Server from the client, you use TDS protocol over TCP. This is fine over reliable LANs but over the internet these connections are relatively slow and fragile, TDS is still used to connect to databases in the cloud, but you need to use a combination of the new features such as connection pools and idle connection resiliency to make applications faster and more reliable. … Read more
Here are seven practical tips for any 'accidental DBA' or developer, faced with having to deploy an upgrade to a database live to production, in a development environment that is in the early stages of getting the database code "house in order", and without any existing 'continuous delivery' process.… Read more
Joe finds a reference to Conway's Game of Life whilst clearing out his desk, and is suddenly gripped with nostalgia. It wasn't just flares, mullets and disco, but simple computer games in interpreted basic. Somehow, Conway s Game of Life was too intriguing to be abandoned in the attic. Can it be implemented in SQL? Joe sets up a challenge.… Read more