Azure SQL DW(SQL Data Warehouse) is a massively parallel, petabyte-scale, cloud solution for data warehousing based on SQL. It is highly elastic and fully managed, allowing you to scale capacity in seconds and set up in minutes. You can scale computing and storage independently by yourself. It will enable you to burst computing of analytical workloads that are complex, or scale down your warehouse for archival scenarios and pay depending on what you are utilizing rather than lock yourself into cluster configurations that are predefined – and obtain better cost efficiency when compared with traditional data warehouse solutions. Read More
Category: SQL Server
Consolidating SQL Server Instance by Clustering and Stacking
NOTES:
- Windows Failover Clustering comprising two nodes.
- Two SQL Server Failover Cluster Instances. This configuration optimizes the hardware. IN01 is preferred on Node1 and IN02 is preferred on Node2.
- Port Numbers: IN01 listens on port 1435 and IN02 listens on port 1436.
- High Availability. Both nodes back up each other. Failover is automatic in case of failure.
- Quorum Mode is Node and Disk majority.
- Backup LAN in place and routine backup configured using Veritas
Understanding Data Sharing in SQL Server Docker Containers
In the previous articles, “Introduction to Docker and Docker Toolbox” and “Docker and SQL Server on Linux“, we discussed a lot about SQL Server Docker containers. As you should know by now, instantiating SQL images is very simple and pretty straightforward thing with the Docker containers.
This article talks about shared volumes and the preferred mechanism for data persistence using the Docker containers. Read More
Registered Server, a Hidden Gem of SQL Server Management Studio. Part 2
In my previous article, I have covered the following topics:
- What are Registered Servers and their usage.
- How to create and configure a Local Server Group and Central Management Studio.
In this article, I am going to demonstrate how to execute a query on multiple servers using Registered Servers. Also, I am going to cover how we can evaluate the database policy on multiple database servers using Registered Servers. Read More
Understanding GROUPING and GROUPING_ID Functions in SQL Server
The ROLLUP and CUBE operators are used to return results aggregated by the columns in the GROUP BY clause.
The GROUPING and GROUPING_ID functions are used to identify whether the columns in the GROUP BY list are aggregated (using the ROLLUP or CUBE operators) or not.
There are two major differences between the GROUPING and GROUPING_ID Functions.
They are as follows:
- The GROUPING function is applicable on a single column, whereas the column list for the GROUPING_ID function has to match the column list in the GROUP BY clause.
- The GROUPING function indicates whether a column in the GROUP BY list is aggregated or not. It returns 1 if the result set is aggregated, and 0 if the result set is not aggregated.
On the other hand, the GROUPING_ID function also returns an integer. However, it performs the binary to decimal conversion after concatenating the outcome of all of the GROUPING functions.
In this article, we will see the GROUPING and GROUPING_ID functions in action with the help of examples. Read More
Different Ways to Compare SQL Server Tables Schema and Data
SQL Server provides us with different solutions to replicate or archive a database table or tables to another database, or the same database with different names. As an SQL Server Developer or Database Administrator, you may face situations when you need to check that the data in these two tables are identical, and if, by mistake, the data is not replicated between these two tables, you need to synchronize the data between the tables. In addition, if you receive an error message, that breaks the data synchronization or replication process, due to schema differences between the source and destination tables, you need to find an easy and fast way to identify the schema differences, ALTER the tables to make the schema identical in both sides and resume the data synchronization process. Read More
Registered Server, a Hidden Gem of SQL Server Management Studio. Part 1
Managing numerous database servers in an organization is difficult. Either we must maintain a list or maintain an Excel file that has a list of database servers. To retrieve basic information about SQL Instance or databases, DBA must query on individual database servers. If a developer wants a DBA to execute an SQL Script on all database servers, DBA must review the list of servers and execute them one by one, which is time-consuming and error-prone. It might happen that DBA missed any server where it was required to execute. Imagine the cases when DBA must execute the script on specific database servers. It becomes very painful. This task can be performed using the PowerShell scripts but again, it requires some complex PowerShell coding. This task can be managed more efficiently using “Registered Servers”. Read More
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.
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:
- Database Migration Assistant
- SQL Server Management Studio Deployment Wizard
- SQL Server to Azure SQL replication
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.
Docker and SQL Server on Linux
SQL Server 2017 is the biggest release and most important releases in the Microsoft product history simply because of the freedom that it gives to install the product anywhere. The advent of the Docker containers and growing emphasis on open source solutions opened the door to greater technological innovation and advancement.
This article discusses the following topics:
- Introduction to Docker container
- Pre-requisite to build Docker container
- A walkthrough of the setup process to build a SQL Server instance running on the Linux CentOS and in a Docker container
- How to connect to the database engine
- How to connect to the database engine externally
- And more…