Azure SQL Server auto-failover groups July 18, 2019 by Ranga Babu In this article, we will review how to set up auto-failover groups in Azure SQL Server and how failover group is different from active geo-replication in Azure. Auto-failover group is an Azure SQL database feature that replicates one or a group of databases to the secondary Azure SQL server in the cross-region. We cannot have a secondary server in the same region. This feature is used to failover all the databases in the failover group in case of disaster and the failover is automatic. Read more »
Overview of Service Principal Name and Kerberos authentication in SQL Server July 18, 2019 by Rajendra Gupta This article gives an overview of Service Principal Name (SPN) for using the Kerberos authentication in SQL Server connections. We use the Kerberos authentication to authenticate windows users securely for providing access to SQL Server. Read more »
SQL Server Lead function overview and examples July 16, 2019 by Rajendra Gupta This article explores the SQL Server Lead function and its usage with various examples.
SQL DROP TABLE statement overview July 16, 2019 by Rajendra Gupta This article gives an overview of the SQL DROP TABLE statement to remove one or more tables from a database. Read more »
Azure SQL database Geo-Replication July 15, 2019 by Ranga Babu In this article, we will review how to set up Geo-Replication on Azure SQL databases. Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server. We can also use this feature to migrate a database from one server to another server in the same or cross region with minimal downtime. Geo-replication uses the Always-on feature to replicate committed transactions to the secondary database asynchronously. Read more »
Understanding the SQL Decimal data type July 15, 2019 by Gauri Mahajan This article aims to walk you through the SQL Decimal data type and its usage with various examples. We will also see how we can exercise this data type in SQL Server to help make SQL developer’s job easier. Read more »
Applying Transaction Logs to the Secondary Replica in SQL Server Always On Availability Groups July 12, 2019 by Rajendra Gupta Introduction SQL Server Always On Availability Groups provides HADR solutions for the SQL databases. Here HA refers to high availability and DR refers to disaster recovery. The priority of this feature is to keep the database highly available and then provide Disaster recovery. Due to this reason, if the secondary replica goes down in a synchronous data commit mode, SQL Server changes commit mode to Asynchronous so that users can continue run the transactions and a secondary replica can be in sync later once it bought up. SQL Listener also points to the primary replica and continues redirects connection to the primary replica. Read more »
Memory-Optimized TempDB Metadata in SQL Server 2019 July 10, 2019 by Ed Pollack Introduction In-memory technologies are one of the greatest ways to improve performance and combat contention in computing today. By removing disk-based storage and the challenge of copying data in and out of memory, query speeds in SQL Server can be improved by orders of magnitude. Read more »
SQL intersect use in SQL Server July 10, 2019 by Daniel Calbimonte Introduction In this article, we will show how to use the SQL intersect logical operator using different examples. Read more »
View Definition Permissions in SQL Server July 9, 2019 by Rajendra Gupta We have various database objects such as view, stored procedures, triggers, functions and indexes in a relational database. Many times, we want to view definitions for these objects. We can use either SSMS graphical way or t-SQL to generate scripts. Read more »
SQL replication with a Publisher Database in Always On Availability Groups July 8, 2019 by Ranga Babu 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.
A walk-through of moving database files in SQL Server Always On Availability Groups July 8, 2019 by Rajendra Gupta SQL 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, 2019 by Rajendra Gupta We 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, 2019 by Ranga Babu In 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, 2019 by Rajendra Gupta We 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, 2019 by Ranga Babu In 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, 2019 by Bojan Petrovic Index 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, 2019 by Rajendra Gupta In 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, 2019 by Sifiso W. Ndlovu One 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, 2019 by Ben Richardson SQL 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, 2019 by Rajendra Gupta Developers 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, 2019 by Rajendra Gupta This 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, 2019 by Ranga Babu This 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, 2019 by Rajendra Gupta It 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, 2019 by Ranga Babu In 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 »
