Currently Browsing: SQL Tips and Tricks
SQL Server / System Stored Procedures / Auto generated System Stored Procedures ‘dt_’
Posted by Barac in SQL Server, SQL Tips and Tricks on Feb 26th, 2020 | 0 comments
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...
SQL Server / PowerShell / Exporting data from a SQL Server table to multiple comma-separated value (CSV) files using PowerShell
Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Jan 28th, 2020 | 0 comments
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]...
Azure SQL Managed Instance / Database Migration Service (DMS) / Online Data Migration / Migrate your database to Azure SQL Managed Instance
Posted by Barac in Azure, SQL Server, SQL Tips and Tricks on Dec 23rd, 2019 | 0 comments
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...
Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Public Endpoint
Posted by Barac in Azure, SQL Server, SQL Tips and Tricks on Sep 5th, 2019 | 0 comments
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...
Azure SQL Managed Instances / SSMS / Connect to SQL Managed Instance / Private Endpoint
Posted by Barac in Azure, SQL Server, SQL Tips and Tricks on Aug 30th, 2019 | 0 comments
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...
SQL Server / SSMS / Expanding Indexes List Query
Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 11th, 2019 | 0 comments
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=' +...
SQL Server / SSMS / Expanding Columns List Query
Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 5th, 2019 | 0 comments
Same as in previous post example Expanding Tables 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 150K tables, some of the tables have more than 500 columns, and expanding columns list operation can end up being slower than usual. Here is the code: SELECT clmns.name AS [Name], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' +...
SQL Server / SSMS / Expanding Tables List Query
Posted by Barac in SQL Server, SQL Tips and Tricks on Mar 3rd, 2019 | 0 comments
Usually I do not even notice this query working in the background, since this operation is quite fast. But recently I end up working with a customer having DB with 150K tables, and expanding table list operation can be really slow and expensive. Here is the code: SELECT tbl.name AS [Name], SCHEMA_NAME(tbl.schema_id) AS [Schema], 'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' AS [Urn],...
PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs
Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Feb 12th, 2019 | 0 comments
Restore multiple TRN logs using PowerShell. In this example I will show you really simple way how you can use PowerShell to restore multiple logs at once. After restoring full backup WITH NORECOVERY you need to figure it out last log sequence number of the last transaction in the backup set. TRN Log backups must be restored in the order in which they were created. Log backups contain log sequence numbers (LSN), and they must be restored in that particular order. There are few ways to find last LSN. USE msdb GO SELECT TOP 10 bck.database_name , bck.first_lsn , bck.last_lsn , bck.backup_start_date ,...
Azure / SQL Server / Azure SQL Database Disaster Recovery / Orphaned Users / Different SIDs
Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Dec 5th, 2018 | 0 comments
If you are using Azure SQL Database with geo-replication or auto failover group and if you are using logins and users rather than contained users you can end up with the following error when you try to connect to the secondary/failover database after failover occured. If you already created a login on the failover server using SQL command -- ====================================================================================== -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database --...
About Me
My name is Zoran, currently living in Auckland, New Zealand. I am a database specialist with more than 13 years of hands-on experience in database administration and optimisation. Also, I am a Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert with a Master‘s degree in Information Technology.
read moreAuckland SQL Group
MSDN PROFILE
