Main concept of SQL Server locking

In this post, we will discuss the SQL Server lock mechanism and how to monitor SQL Server locking with SQL Server standard dynamic management views. Before we start to explain SQL Server lock architecture, let’s take a moment to describe what the ACID (Atomicity, Consistency, Isolation, and Durability) database is. The ACID database can be explained as database theory. If a database is called relational database, it has to meet Atomicity, Consistency, Isolation, and Durability requirements. Now, we will explain these requirements briefly.

Read More

Migrating SQL Server Database to Azure SQL (PaaS)

In this article, we will highlight SQL Server Data migration to Azure SQL (PaaS).  You can find different approaches for this migration process because SQL Server offers different types of tools and features for this migration. Some of these are:

These migration approaches have some pros and cons. But if you ask my opinion, Data Migration Assistant is better than others because, in this migration process, you will face fewer migration problems or issues. Data Migration Assistant will be more helpful to handle these issues.

In this article, we will specifically mention Database Migration Assistant, also known as DMA. At the same time, we will make a small demo of SQL Server Management Studio Deployment Wizard.

Read More

Choosing Azure SQL Purchase Model and Service Tier

When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:

  • Which purchase model is suitable for my apps and business requirements?
  • How much budget do I need?
  • Which performance level meets my requirements?
  • Can I achieve the acceptable performance of my apps?

Read More

Azure SQL Geo-Replication and Failover Groups

Azure SQL offers different types of business continuity solutions. One of these solutions is Geo-Replication that provides an asynchronous database copy. You can store this copy in the same or different data center locations (regions).  There can be four readable database copies. In the documentation of Microsoft notes, the recovery point objective (RPO is the maximum acceptable amount of data loss measured in time) is less than 5 seconds.  If we want to automate and make (users will not affect) failover mechanism transparent, we have to create the auto-failover group.

Read More

Monitoring Azure SQL Database with Azure SQL Analytics

The most important and challenging responsibility of a database administrator is monitoring performance metrics. Because monitoring performance and troubleshooting performance issues are considered to be difficult. For this reason, we need diagnostic and monitoring tools to measure performance counters and metrics. For Azure SQL there is a tool which is named SQL Analytics. With this tool, we can measure and monitor Azure SQL databases and elastic pools. At the same time, we can create alerts for notifications. SQL Analytics offers performance metrics in graphical form. In this article, we will learn how to enable Azure SQL Analytics. Read More

Azure SQL Database Automatic Tuning

Microsoft has recently announced an incredible new feature – automatic tuning in Azure SQL Database. To be honest, I am thoroughly impressed with this feature because Microsoft engineers have sophisticatedly used artificial intelligence in SQL Azure performance tuning. The aim is to monitor Azure SQL database and send these observations to the built-in intelligence service that generates some recommendations. They can be applied at offpeak times. This feature has also simplified the work of database administrators; they don’t have to worry about SQL Azure database performance now.

Read More

SQL Server Table Partitioning

Problem

In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.

Why do we need table partitioning?

Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:

  • Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
  • Back up.
  • Lock problems.

Read More