In this article, we will review how to setup SQL replication with publisher database in Always On availability groups so that replication continuous to sync even after failover to the secondary server in the availability group.
Read more »
A walk-through of moving database files in SQL Server Always On Availability Groups
July 8, 2019SQL Server Always On Availability Groups feature provides high availability (HA) and disaster recovery solutions. We can add multiple databases in a single Always on Availability Group on SQL Server Enterprise edition. Before we add a database in the AG group, we need to use the following checklist.
Read more »
Difference between SQL Truncate and SQL Delete statements in SQL Server
July 8, 2019We get the requirement to remove the data from the relational SQL table. We can use both SQL Delete and SQL Truncate statement to delete the data. Understanding differences between these commands helps SQL developers to handle their data well. Additionally, this is a very common question asked in SQL beginner’s interviews.
Read more »
Azure SQL Data Sync – Replicate data and schema changes between Azure SQL databases
July 5, 2019In this article, we will review how to configure the sync group to replicate data between Azure SQL databases using Azure SQL Data Sync.
Read more »
Overview of SQL RANK functions
July 3, 2019We perform calculations on data using various aggregated functions such as Max, Min, and AVG. We get a single output row using these functions. SQL Sever provides SQL RANK functions to specify rank for individual fields as per the categorizations. It returns an aggregated value for each participating row. SQL RANK functions also knows as Window Functions.
Read more »
DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key
July 3, 2019In this article, we will review on DELETE CASCADE AND UPDATE CASCADE rules in SQL Server foreign key with different examples.
Read more »
Optimizing SQL Server index strategies
July 2, 2019Index strategies overview
This article is about techniques for optimizing the SQL Server indexes strategy. It is an appendix of the SQL index overview and strategy article in which I covered different areas like what indexes actually do, how to create them, and I briefly mentioned some index design guidelines. Furthermore, I also presented an example of how to design them by tuning and optimizing queries, so I’ve really tried to cover all but there is always more when it comes to SQL Server indexes.
Read more »
SQL View – A complete introduction and walk-through
July 1, 2019In relational databases, data is structured using various database objects like tables, stored procedure, views, clusters etc. This article aims to walk you through ‘SQL VIEW’ – one of the widely-used database objects in SQL Server.
Read more »
Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events
July 1, 2019One shared characteristic among popular data visualization tools such as Power BI is the ease at which data can be extracted from a variety of disparate data sources, usually at a click of a button, as shown in Figure 1. Such convenience, though, tends to come at a cost as you often have little control over how background scripts used to extract data are generated. Yet, this should be of utmost concern for data architects and BI developers alike as rarely do you find auto-generated scripts that are efficient and optimal. In this article, join me as I put on my DBA hat and trace, monitor and review SQL batch statements that are auto-generated by the Get Data feature in Power BI using SQL Server Extended Events.
Read more »
Understanding the SQL Server CASE statement
June 28, 2019SQL Server CASE statement is equivalent to the IF-THEN statement in Excel.
Read more »
Overview of SQL Server Rounding Functions – SQL Round, Ceiling and Floor
June 26, 2019Developers deal with numerous data types on a day- to-day basis. We need to change the data type or format as per the user requirement. We use ‘SQL Server rounding function’ like SQL Round, Ceiling and Floor to round the values to the nearest numbers. We perform an arithmetic calculation on data as well. It is a challenging task to change the value of a number to an approximate number. We do not want to display decimal numbers in the application front end.
Read more »
Overview of the SQL Count Distinct Function
June 26, 2019This article explores SQL Count Distinct operator for eliminating the duplicate rows in the result set.
Read more »
Azure SQL – Index tables using Azure Automation
June 25, 2019This article provides an overview of indexing tables in Azure SQL database using Azure Automation and how to schedule indexing jobs to run at specific intervals.
Read more »
Get details of SQL Server Database Growth and Shrink Events
June 25, 2019It is essential for the DBA to need to ensure the SQL Server database performance. Performance tuning is an open-ended task, and you need to ensure the monitoring of various database parameters.
Read more »
Azure SQL – Elastic Job Agent
June 24, 2019In this article, we will review on elastic job Agent in Azure SQL and how to configure elastic jobs to run scripts on Azure SQL databases. SQL Server Agent is a powerful component that is used to schedule and execute jobs in SQL server. But in Azure, SQL server agent is available only in managed instances and not in the single databases. To schedule and execute jobs on single databases we have a feature called elastic job agent. This feature is used for scheduling and execute jobs on a single database, all the databases in the server, or on all the databases in an elastic pool.
Read more »
Clustered Index vs. Heap in SQL Server
June 21, 2019Summary
There are few topics so widely misunderstood and that generates such frequent bad advice as that of the decision of how to index a table. Specifically, the decision to use a heap over a clustered index is one where misinformation spreads quite frequently.
Read more »
A step-by-step walkthrough of SQL Inner Join
June 21, 2019Organizations are generating and analyzing unmatched volumes of data with each passing minute. In this article, we will demonstrate how we can employ SQL Inner Join to query and access data from multiple tables that store this incessantly growing data in the SQL databases.
Script SQL Server objects using DBATools
June 20, 2019This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools.
Database administrators or developers require generating scripts for SQL Server objects. We might need scripts to store a copy of object script before object change, create specific objects into other database environments such as development, UAT or non-prod environment. It is an excellent practice to keep a copy of the object before making a change to it. We can easily refer to the old script and roll back if required. Usually, we use SSMS Generate Scripts wizard to get these scripts.
Read more »
Azure Kubernetes Service (AKS) – Managing SQL Server database files
June 18, 2019In this article, we will review on managing database files of SQL Server running on Azure Kubernetes service.
EXEC SQL overview and examples
June 18, 2019In this article, we will review on EXEC SQL statement in SQL Server and explore a few examples.
Read more »
Isolation levels behavior in SQL Server Always On Availability Groups
June 18, 2019In my previous article, Data synchronization in SQL Server Always On Availability Groups, we explored the internal data synchronization for both Asynchronous and Synchronous data commit mode. In the following image, on the secondary replica, we can see two important processes.
Read more »
Automatic Seeding in Always On Availability Groups
June 18, 2019Introduction
In SQL Server Always On Availability Groups, we can add a database into an existing availability group using different methods. We get the following option in the Initial Data Synchronization wizard in SSMS.
Read more »
Overview of SQL Server Ports
June 17, 2019This article is useful for a beginner in SQL Server administration and gives insights about the SQL Server Ports, the methods to identify currently configured ports.
Read more »
Situations When We May Want Higher Azure Costs
June 17, 2019We’ve looked at methods to reduce costs within Azure. We may experience situations where a slight increase in Azure costs will benefit us to help protect our resources and customers when it involves security or other critical updates. When we consider these situations, we must keep up-to-date with the latest patches, updates to development libraries, as well as the possible effects of these updates to our existing code. Likewise, related to resource usage, a resource may be unused or seldom used by a percent of our customers that we keep when we’re ready to switch all our customers while we make the appropriate upgrades to our resources to help with costs. We’ll look at some techniques that we can use to manage the challenge of critical updates while also keeping costs down, or putting costs into a context about what may be more expensive.
Read more »
Vulnerability Assessment and Advanced Threat Protection in Azure SQL Database
June 17, 2019In today’s time where data breaches are highly expected to happen, there is a high need to ensure sensitive data in the Azure SQL databases is secured and which is why data security implementation is gaining momentum across each community. We covered one of the features of Advanced Data Security here in Advanced Data Security in Azure SQL Database – Data Discovery & Classification. Let’s dive further into the other two features that Azure SQL offers with this capability in this article.
Read more »