Currently Browsing: SQL Tips and Tricks

SQL Server / Remove SCHEMABINDING from the multiple VIEWs without DROP and CREATE statements

Posted by in SQL Server, SQL Tips and Tricks on Mar 12th, 2022 | 0 comments
SQL Server / Remove SCHEMABINDING from the multiple VIEWs without DROP and CREATE statements
What is the SCHEMABINDING within VIEW?It simple binds the view to the schema of the underlying objects. Tables behind the View cannot be altered in any way which could affect view definition. You can find more at this MS article: ALTER VIEW (Transact-SQL) There is a easy way to remove SCHEMABINDING, you just need ALTER VIEW statement. But what if you have 100 views that needs to be altered? Well there are no SWITCH OFF button for SCHEMABINDING in SQL Server. I had same situation and I wrote this simple script which will generate ALTER VIEW statements WITHOUT SCHEMABINDING for you for every single VIEW...
read more

SQL Server / Configure an Azure Load Balancer for a SQL Server Always On AG in Azure Virtual Machines / Possible Floating IP and Health Probes Connectivity and Networking issues

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Jan 21st, 2022 | 1 comment
SQL Server / Configure an Azure Load Balancer for a SQL Server Always On AG in Azure Virtual Machines / Possible Floating IP and Health Probes Connectivity and Networking issues
I will take you through the steps on how to properly configure Azure Load Balancer with Azure SQL Server VMs (IaaS), Windows Failover Cluster (WFC),  Availability Group AG, and listener endpoint. The following link shows us what is the best Microsoft Practice, and how Microsoft suggests we should configure an Internal Load Balancer (ILB) for the Availability Group Listener. Create & configure the load balancer   How LB really works with Always On Availability Group on SQL Server on Azure VMs? There are two types of Load Balancers  – Internal load balancers which balance traffic within a VNET...
read more

SQL Server / Availability Group / Readable Secondary Replica Delay / Parallel Redo Operations

Posted by in SQL Monitoring, SQL Server, SQL Tips and Tricks on Apr 10th, 2021 | 0 comments
SQL Server / Availability Group / Readable Secondary Replica Delay / Parallel Redo Operations
One of the most common issues with Availability Group and using readable secondary replica is with Parallel REDO operations. On your readable secondary replica, you will notice a few different types of waits commonly related to this, but in this article, I will cover just one of them PARALLEL_REDO_TRAN_TURN PARALLEL_REDO_TRAN_TURN This type of waits happens in readable secondary replica and it is caused by page splits or forwarded records on heap tables triggered by new inserts or updates on primary replica. There are few ways to fix this issue– Reduce the number of pages splits on the primary...
read more

SQL Server / BACPAC / Could not extract package from specified database – The wait operation timed out

Posted by in SQL Server, SQL Tips and Tricks on May 13th, 2020 | 0 comments
SQL Server / BACPAC / Could not extract package from specified database – The wait operation timed out
Export Data-tier Application. This is usually really simple and straight forward process. But what if you have database with over 100K tables, 200K indexes? You would probably end up with following timeout error during export process using SSMS To resolve this possible “Execution Timeout Expired” issue you may use SqlPackage.exe command-line utility to export your database including database schema and user data from SQL Server Database to a BACPAC package (.bacpac file)  More information about this command-line utility tool and how to install and from where to download you can find on the...
read more

SQL Server / System Stored Procedures / Auto generated System Stored Procedures ‘dt_’

Posted by in SQL Server, SQL Tips and Tricks on Feb 26th, 2020 | 0 comments
SQL Server / System Stored Procedures / Auto generated System Stored Procedures ‘dt_’
Searching through my staging/test database and looking for ‘text’, ‘ntext’ data types I stumbled upon 31 system stored procedure starting with the prefix ‘dt_’.  I was mostly interested in two of them using text datatype as an input parameter. So what are those Stored Procedures? Using different Visual Tools against SQL Server, such as Enterprise Manager or design tools, will trigger auto generated code and create set of System Stored Procedures. Prefix ‘dt_’ stands for ‘DaVinci Tools’ early code name for technology used to auto generate...
read more

SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell

Posted by in Powershell, SQL Server, SQL Tips and Tricks on Jan 28th, 2020 | 0 comments
SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell
Export SQL Data to multiple CSV files. There are many different ways to do this (SSIS, TSQL) but I think that PowerShell could be really efficient tool for such small random tasks. For this example I will use test table with 17220 rows. First step is to export whole table to CSV file using export-csv cmdlet.  Export-CSV converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file. If you want to find more details about Export-CSV cmdlet and see more usage examples you can read this MS article. param ( [String] $destination = 'your_destination', [String]...
read more

Azure SQL Managed Instance / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance

Posted by in Azure, SQL Server, SQL Tips and Tricks on Dec 23rd, 2019 | 0 comments
Azure SQL Managed Instance / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance
Using Azure Database Migration Service (DMS) you can easily migrate your databases from multiple sources to Azure Data platforms with minimal downtime. For this example we are going to use following: Azure Database Migration Service (DMS), Premium tier (4 vCores) DMS Project: Online Data Migration Source: SQL Server 2017, 8CPU, 64GB Memory Target: Azure SQL Managed Instance, 16 vCores, 80GB Memory   More details about DMS and how to download and install you can find in this MS article. First step we will be to deploy Database Migration Service. Specify a name for the database migration service, and...
read more

Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Public Endpoint

Posted by in Azure, SQL Server, SQL Tips and Tricks on Sep 5th, 2019 | 0 comments
Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Public Endpoint
In the previous post we had example how to connect to Azure SQL Managed Instance from Azure VM within same VNet using private endpoint Now I will show you how to connect to the SQL Managed Instance from your remote location using public endpoint. First go to your SQL MI and select Virtual Network    Enable Public Endpoint (data) option Public endpoint provides the ability to connect to Managed Instance from the Internet without using VPN and is for data communication (TDS) only.   After enabling your SQL MI will have 2 connection strings, private and public For the remote access via public...
read more

Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Private Endpoint

Posted by in Azure, SQL Server, SQL Tips and Tricks on Aug 30th, 2019 | 0 comments
Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Private Endpoint
There are different ways to connect to SQL Managed Instance using SSMS. One of the easiest ways to do that is using Private Endpoint. Azure SQL Managed Instance provides a private endpoint to allow connectivity from inside its virtual network. In this example we are going to use azure VM within the same Virtual Network as SQL Managed Instance. You should create VM inside the same VNet but different subnet. When you create SQL Managed Instance you will have following resources in you Resource Group: SQL managed instance Virtual Network Network security group Route table Virtual cluster As well you will...
read more

SQL Server / SSMS / Expanding Indexes List Query

Posted by in SQL Server, SQL Tips and Tricks on Mar 11th, 2019 | 0 comments
SQL Server / SSMS / Expanding Indexes List Query
Same as in previous posts examples Expanding Tables List Query and Expanding Columns List Query I usually do not notice this type of queries working in the background. But recently I end up working with a customer having DB with over 500K indexes, 150K tables and expanding indexes list operation can end up being slower than usual. Here is the code: SELECT i.name AS [Name], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' +...
read more

« Previous Entries