When thinking about migration the most common issue is downtime. There are some methods that require downtime like backup/restore or less downtime using Log Shipping.
You can choose several different methods to migrate your database. For example, methods that require downtime include:
Backup the database, manually copy the backup file to the machine and then restore the database there. This method is simple and the use of compression minimizes the time that is required.
Perform a backup to an Azure Blob storage account, and then restore the database from that backup. This method removes the necessity to manually copy the backup file.
Detach the database, copy the files to an Azure Blob storage and then attach them to the SQL Server instance. Use this method if you plan to store database files in Azure Blob storage permanently instead of on a hard disk.
Log Shipping, use a backup/restore process and the downtime depends on the transaction log file backup copying a small file in the process. This method is simple and is necessary a connection between the machines and permission to copy the backup files.
Methods that not require downtime:
AlwaysOn, adding a new secondary replica after replication has completed, you can failover to make the machine the primary replica. For Azure virtual machine use the Add Azure Replica Wizard.
Transactional Replication will minimize downtime, but don’t have an AlwaysOn deployment in your SQL Server system.
If you need help to decide which method is better for your scenario, feel free to contact me.
SqlPackage is a command-line utility that you can use for exporting and importing operations in both on-premises SQL Server databases and in cloud databases. SqlPackage supports the following operations:
Extract. Creates a database snapshot DACPAC file from a SQL Server database or from Azure SQL Database.
Publish. Updates the schema in a live database to match the schema in a DACPAC
file. If the database does not exist on the destination server, the publish operation
Export. Exports both schema and data from a SQL Server database or from Azure SQL Database into a BACPAC file.
Import. Imports the schema and data from a BACPAC into a new database.
DeployReport. Creates an XML report that describes the changes that would be made by a publish operation.
DriftReport. Creates an XML report of the changes that have been made to a registered database.
Script. Creates a Transact-SQL script that you can use to update the schema of a target database to match the schema of a source database.
Use the /Action: or /a parameter to specify which action to execute.
SqlPackage example to import from bacpac file to Azure sqlpackage.exe /Action:Import /tsn:tcp:.database.windows.net,1433 /tdn: /tu: /tp: /sf: /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P4 /p:Storage=File
Stretch Database is a feature of SQL Server where data can be split between on-premises storage and cloud storage. With Stretch Database, cold, historical data is kept in the cloud and active data is kept on-premises for maximum performance.
Stretch Database requires no changes to client applications or existing Transact-SQL queries, so you can implement it seamlessly for existing applications. Stretch Database can reduce on-premises storage requirements both for data and associated backups. Backups of on-premises data are smaller and therefore run quicker than standard backups. Data in the cloud is backed up automatically.
With Stretch Database, cold historic data remains available for users to query, although there might be a small amount of additional latency associated with queries.
Implement a Stretch Database
You can implement Stretch Database entirely within SQL Server Management Studio; you do not need to pre-configure servers or storage within Microsoft Azure.
Implementing Stretch Database involves the following steps:
Start Microsoft SQL Server Management Studio and connect to the instance of SQL Server.
In Object Explorer, expand Databases.
Right-click the database, point to Tasks, point to Stretch, and then click Enable.
Complete the steps in the Enable Database for Stretch wizard to create a Database Master Key; identify the appropriate tables and configure the Microsoft Azure deployment.
After implementing Stretch Database, you can monitor it from SQL Server Management Studio.
In Object Explorer, expand Databases, right-click the stretch-enabled database, point to Tasks, point to Stretch, and then click Monitor to open the Stretch Database Monitor. This monitor shows information about both the local and Azure SQL instances, along with data migration status.
Parallelism refers to multiple processors cooperating to execute a single query at the same time.
Parallel execution involves the overhead of synchronizing and monitoring the tasks. So, that’s why parallel plans are considered expensive operations.
The query optimizer determines whether a parallel plan should be used based on the configuration and the query cost.
A configuration value, max degree of parallelism determines how many CPUs can be used to execute a query. Also, using query hint can set how many CPUs can be used for that specific query.
Cost threshold for parallelism determines the cost that a query must meet before a parallel query plan will even be considered. Query cost is determined based on the amount of data the query optimizer estimates to be read to complete the operation.
In earlier versions of SQL Server, it was common to disable parallel queries on systems that were primarily used for transaction processing. It’s common to see MAXDOP 1 for OLTP environments. I prefer to raise the cost threshold for parallelism so a parallel plan is only considered for higher cost queries.
Let’s see an example.
I executed the query below and I got parallelism for that. My query cost was 374.148, remember this cost is always estimated.
SELECT * FROM dbo.FactInternetSales AS FIS
LEFT JOIN dbo.DimCustomer AS DC ON DC.CustomerKey = FIS.CustomerKey
LEFT JOIN dbo.DimProduct AS DP ON DP.ProductKey = FIS.ProductKey
LEFT JOIN dbo.DimProductSubcategory AS DPS ON DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
ORDER BY DP.StandardCost
Increasing the cost threshold for parallelism value to 400 you are not going to see any change in the query, because the real cost isn’t 374, that cost is for only 1 thread.
In my case the max degree of parallelism value is 2, so multiplying 374 x 2 = 748
Setting the value to 800 and run the same query again you can see no parallelism and the cost is 691.208, so it fits in the cost value.
If your CXPACKET wait counter is high, try to increase the cost threshold for parallelism and not set MAXDOP to 1 because some queries even in a transaction database will have the benefit to run in parallel.
Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create.
In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage I/O, tempdb uses the same file structure as a user database one or more data files and a log file. The arrangement of data pages within tempdb data files is also based on the same architecture as user databases.
Unlike all other databases, SQL Server recreates the tempdb database each time the SQL Server service starts. This is because tempdb is a temporary store.
There are three primary ways that the organization of tempdb files can affect system performance:
Because users and the database engine both use tempdb to hold large temporary objects, it is common for tempdb memory requirements to exceed the capacity of the buffer pool in which case, the data will spool to the I/O subsystem. The performance of the I/O subsystem that holds tempdb data files can therefore significantly impact the performance of the system as a whole. If the performance of tempdb is a bottleneck in your system, you might decide to place tempdb files on very fast storage, such as an array of SSDs.
Although it uses the same file structure, tempdb has a usage pattern unlike user databases. By their nature, objects in tempdb are likely to be short-lived, and might be created and dropped in large numbers. Under certain workloads especially those that make heavy use of temporary objects this can lead to heavy contention for special system data pages, which can mean a significant drop in
performance. One mitigation for this problem is to create multiple data files for tempdb; this is covered in more detail in the next topic.
When SQL Server recreates the tempdb database following a restart of the SQL Server service, the size of the tempdb files returns to a preconfigured value. The tempdb data files and log file are configured to autogrow by default, so if subsequent workloads require more space in tempdb than is currently available, SQL Server will request more disk space from the operating system. If the initial
size of tempdb and the autogrowth increment set on the data files is small, SQL Server might need to request additional disk space for tempdb many times before it reaches a stable size.