Database migration methods

migration-logo-2389341When 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 a tool to import/export SQL Server and Azure SQL DB

repair_database-512SqlPackage 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
    creates it.
  • 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

More about SqlPackage parameters:
https://msdn.microsoft.com/en-us/library/hh550080%28v=vs.103%29.aspx?f=255&MSPPError=-2147217396

SQL Operations Studio Dashboards

SQL Operations Studio is a free tool that runs on Windows, macOS, and Linux, for managing SQL Server databases.

I have a great experience running this tool on Linux CentOS and you can build server and database management dashboards  like below.dashboard02dashboard03

To create custom dashboards check out my tip https://www.mssqltips.com/sqlservertip/5434/customize-sql-operations-studio-dashboards/

 

What is Stretch Database?

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.

img2

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:

  1. Start Microsoft SQL Server Management Studio and connect to the instance of SQL Server.
  2. In Object Explorer, expand Databases.
  3. Right-click the database, point to Tasks, point to Stretch, and then click Enable.

scretch

scretch2

scretch3scretch4scretch5Complete 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.

Moving the msdb, model, and tempdb databases files

All system databases, except the resource database, can be moved to new locations to help balance I/O load.

To move the msdb, model, and tempdb databases, perform the following steps:

  • For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement.
  • Stop the instance of SQL Server.
  • Move the files to the new location (this step is not necessary for tempdb, as its files are recreated automatically on startup).
  • Restart the instance of SQL Server.

The process for moving the master database is different from the process for other databases. To move the master database, perform the following steps:

  • Open SQL Server Configuration Manager.
  • In the SQL Server Services node, right-click the instance of SQL Server, click Properties, and then click the Startup Parameters tab.
  • Edit the Startup Parameters values to point to the planned location for the master database data (-d parameter) and log (-l parameter) files.
  • Stop the instance of SQL Server.
  • Move the master.mdf and mastlog.ldf files to the new location.
  • Restart the instance of SQL Server

 

Understanding parallelism on SQL Server

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
OPTION(RECOMPILE)

parallel

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

sp_configure

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.

parallel2

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 summary

 

TempDB-Defaults-e1452024871991
The new tempdb tab in SQL server

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.