SQL Server management, tutorials, scripts, coding, programming and tips for database administrators

MS SQL

Kafka Integration with HDInsight 04/23/2018

Microsoft HDInsight is the cloud service that deploys and provisions Hadoop clusters on the Azure cloud. It's a completely managed, open source analytics service to support enterprise needs and supports a wide variety of scenarios with the help of open source frameworks like Hadoop, Storm, Spark, R Server and Hive. Read on to learn more.

Introduction to Azure SQL Managed Instance 04/05/2018

Microsoft currently offers two built-in methods of running production SQL Server databases in Azure. The first relies on the ability of Infrastructure-as-a-Service (IaaS) Azure virtual machines to host a variety of on-premises workloads, including SQL Server instances. The second one leverages Platform-as-a-Service (PaaS)-based Azure SQL Database. With the introduction of the Azure SQL Managed Instance service, you have a third option, which combines the benefits of its two predecessors.

How to Programmatically Identify When Your SQL Server Was Last Started 04/02/2018

Greg Larsen shows you a quick trick to programmatically script a way to identify when SQL Server was last started.

How to Show the Execution Plan of a Running Query 04/02/2018

By looking at a query execution plan, you can determine which steps are used to resolve the query. But what if you just want to find out what the query plans are for only the queries that are currently running?

Monitor Query Performance Using Query Store in SQL Server 03/19/2018

The Query Store captures a history of queries, execution plans, and runtime statistics, which persist within SQL Server and can be reviewed later. Read on to learn more.

Controlling Access to Azure Cosmos DB 03/08/2018

Azure Cosmos DB offers features that facilitate data availability and resiliency. However, there is also a different, very important aspect of facilitating access to distributed data sources in the most optimal manner, which focuses on access control mechanisms. Read on to learn the specifics of this aspect, as it applies to Azure Cosmos DB.

How to Send a TDE Encrypted Backup to Someone Outside Your Organization 03/01/2018

Once you enable your database to be encrypted with Transparent Data Encryption (TDE), the physical database files, and the database backups are encrypted.  If your database and database backup are encrypted, then how can you send the encrypted backup to a person outside your organization?

What is the State of My Transparent Data Encrypted Database? 03/01/2018

When using Transparent Data Encryption, you might wonder “What is the state of my transparent data encrypted database?” Read on to learn the many different states that a transparent data encrypted database might go through.

Data Encryption Using Always Encrypted in SQL Server 02/19/2018

The key challenge for application developers is to build a foolproof system to ensure data security. This is not only more intense, but more complex these days, as many organizations are planning to move their applications to the cloud. Read on to learn more.

Azure Cosmos DB Partitioning 02/05/2018

One of the most important design decisions that must be made when planning deployment of Azure Cosmos DB involves logical partitioning of data that will populate target collections, graphs, or tables. Selecting the optimal partitioning model has both performance and pricing implications. In this article, we will explore the rationale behind these implications and review the partitioning options.

How to Enable Transparent Data Encryption 02/01/2018

By default, SQL Server does not encrypt data in a SQL Server database in an encrypted format. When SQL Server 2008 was introduced, Microsoft implemented Transparent Data Encryption (TDE). When TDE is enabled on a database SQL Server will encrypt the database as data is written to the disk. Read on to learn more...

How to Move a TDE Encryption Key to Another SQL Server Instance 02/01/2018

If you have a database backup of a Transparent Data Encryption (TDE) enabled database, the database backup will contain encrypted data. Because the database backup contains encrypted data you can’t just restore it to any instance. You can only restore the database backup to an instance that contains the same certificate used to originally encrypt the database.

How to Implement Dynamic Data Masking in Azure SQL Database and SQL Server 01/22/2018

Anoop Kumar shows you the SQL Commands (T-SQL) to secure PII/PHI data using the SQL Server Dynamic Data Masking (DDM) feature and explores DDM's built in functions to mask all critical data elements. DDM restricts unauthorized users from accessing critical data with no changes in the application.

Azure Cosmos DB Consistency Levels 01/04/2018

The majority of traditional database management systems are responsible for making sure that any changes to their data are consistently reflected in the results of subsequent queries. Azure Cosmos DB extends the range of consistency options by providing support for bounded-staleness, session, and consistent prefix models.

How to Change the Default Backup Directory 01/02/2018

When you install a SQL Server instance you have the opportunity to identify the default location for your instance database backups. Overtime you might decide you want to change the default location for database backups. This tip shows you two different ways to change the default location for your database backups.