SQL Server / Forwarded Records / Rebuild the Heap Tables Script
Posted by Barac in SQL Server on Jun 9th, 2020 | 0 comments
I was working with a customer having DB with more than 100K HEAP tables. Number of Forwarded Records on some tables were huge. They are using Ola Hallengren‘s scripts to maintain non-clustered indexes on those tables, but rebuilding those indexes do not fix the forwarded records on those HEAP tables. You actually need to rebuild the HEAP ALTER TABLE TableName REBUILD; Or to add a clustered index to the table (permanent fix). They are in the middle of the remodeling their DB so adding clustered indexes on those HEAP tables was not an option....
SQLSATURDAY 986 / Brisbane, Australia / Virtual Event
Posted by Barac in SQL Server, SQLSATURDAY on May 25th, 2020 | 0 comments
VIRTUAL EVENT PASS SQLSaturday is a free training event for professionals who use the Microsoft data platform. These community events offer content across data management, cloud and hybrid architecture, analytics, business intelligence, AI, and more. SQLSaturday Brisbane 2020 is back for 2020. They are going virtual in 2020. Due to the Covid-19 global impact they are still hosting the event, but it will be 100% virtual to keep all the attendees and speakers safe. Time and Date: Saturday, May 30, 2020 Add to Calendar More information:...
Auckland SQL User Group Meetup Event / Online Event / Kevin Kline, Microsoft MVP / 5 Critical Considerations When Moving to the Cloud
Posted by Barac in SQL Server on May 14th, 2020 | 0 comments
ONLINE EVENT Join Kevin Kline as he talks to the Auckland SQL User Group TOPIC: “5 Critical Considerations When Moving to the Cloud.” 5 Critical Considerations When Moving to the Cloud Wednesday, May 20, 2020, 11:30 AM Online event , 36 database professionals Went Join Kevin Kline as he talks to the Auckland SQL User Group about “5 Critical Considerations When Moving to the Cloud.” **RSVP to get the Teams Live Event details** Migrating an existing on-premises SQL Server application to the cloud can be a daunting...
SQL Server / BACPAC / Could not extract package from specified database – The wait operation timed out
Posted by Barac in SQL Server, SQL Tips and Tricks on May 13th, 2020 | 0 comments
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...
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...
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 (...
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...
Azure SQL Managed Instance / Migration / Database Experimentation Assistant (DEA) / Evaluate your workload before you migrate your database to Azure SQL Managed Instance
Posted by Barac in Azure, SQL Server on Nov 11th, 2019 | 0 comments
Using Database Experimentation Assistant (DEA) you can evaluate your current production workload from your current environment and analyze how will that workload perform in your new targeted environment. For this example we are going to use following: Source: SQL Server 2017, 32CPU, 256GB Memory Target: Azure SQL Managed Instance, BC Gen5 (16 vCores, 80GB Memory) DEA VM: SQL Server 2017, 4CPU, 32GB Memory Workload: 10minutes XE trace (XEL), size 19.9GB I will use DEA to capture, analyze and compare source and target...
Azure SQL Managed Instance / Migration / Data Migration Assistant (DMA) / Assess on-premises SQL Server instance
Posted by Barac in Azure, SQL Server on Oct 30th, 2019 | 0 comments
Before we migrate our database to the cloud we need to assess our on-premises SQL Server instance and Data Migration Assistant (DMA) tool can help us with that. Using Data Migration Assistant (DMA) tool we can detect potential compatibility issues which may impact our database functionality after migration to Azure SQL managed Instance. DMA main functionalities: Discover issues that can affect an upgrade to an on-premises SQL Server Discover new features in the target SQL Server platform Migrate an on-premises SQL Server instance to a modern...
Azure SQL Managed Instance / Migration from on-premises SQL Server instance to Azure SQL Managed Instance
Posted by Barac in Azure, SQL Server on Oct 9th, 2019 | 0 comments
Database migration from on premises SQL Server instance (SQL Server 2017) to Azure SQL Managed Instance. With series of posts I will try to describe whole database migration process from on premise SQL Server to the cloud. First step will be to determine whether Azure SQL Managed Instance is compatible with the database requirements of business application. Step 1. Assess managed instance compatibility I used Data Migration Assistant (DMA) to detect potential compatibility issues impacting database functionality on Azure SQL managed Instance....
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
