Tips to Improving SQL Server Backup Performance

SQLServerF1

Backups are one of the most important and core DBA task which every DBA needs to expertise. If there are any short comings or issues with backup planning, implementation, monitoring and testing, then it can result in data loss or can increase the time taken to recover the data with data loss. SQL Server supports multiple databases on SQL Server instances with each database size can be small of few MBs to very large in 100s of GBs to Tera Bytes TBs of late. There is additional care to be taken to plan and implement back strategy for large databases as the time taken to perform the backup increases significantly, so we need to diligently use the different backup methods available to backup the large databases like performing weekly full backups, daily differential backup and every 10-15 minutes log backups. The backups are important even when there are High availability and disaster recovery solutions implemented.

As a DBA some times we may need to look into possibilities of speeding up the backup process to save time taken for the backups of critical databases which are used 24×7. Performing backup can slowdown the databases to an extent due to IO load put by the backup on reading from database files and writing to backup file. There are different ways which we can consider to speed up the SQL Server database backup process to avoid problems for critical servers. Some of the methods or things which we can consider are below. It is important to note that some of the below mentioned options may be available only in latest versions of SQL Server instances, like backup compression available only starting with SQL Server 2008.

– Starting with SQL Server 2008, Microsoft introduced compressed backup. Using the backup compression not only reduces the size of the backup files, but also reduces the amount of IO performed to write to backup files, thus reducing the amount of time taken to perform the backups.

– SQL Server backups allow us to perform backup of one database into multiple backup files, thus increases the use of parallelism which speeds up the backup process. If we point the multiple backup files to different disk drives, then this will further spread out the IO load and makes the backup even faster. We can split database backup to be performed to many backup files, but we need to find the best number of files, as if we use too many files can impact negatively too.

– Some environments, it is common to perform backup directly to the network share or tape drives, which will cause the backup to take more time because the data has to be moved across network. If the backup of large databases is taking long time, then we can consider performing backup locally first to dedicated backup drive and then move the backup file from local backup drive to backup share or tape drive.

– We need to ensure that file system level backups and SQL Server backup timings does not overlap, as both running together will slowdown the system and causes the backup to take more time.

– For large databases we can choose alternative backup plans like performing weekly full backups, daily differential backup and every 10-15 minutes log backup, this we can restrict long time of full backups to weekly once. Differential backups are often fast compared to full backups. It is important to move all full, differential and log backups to backup share or tape so that they can be used in case the server or drive where backups are performed is lost or corrupted.

– We can also use some of the backup parameters like BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE to improve the backup performance. There is no good or bad values for these parameters, it is trial and error approach, where we need to test different values and come to conclusion on what works best for your environment.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Administering SQL Server Report Server Databases

SQLServerF1

Once DBAs install and configure SQL Server Reporting Servers, two new databases will get created in the respective SQL Server instance selected during Reporting Services configuration. By default, these two new databases names will be ReportServer and ReportServerTempdb. As part of Reporting Services configuration, we can choose different names too, but in general, if we choose not to change default names, then we will see these two new databases being created in the respective SQL Server instance.

ReportServer Database – The report server database is a SQL Server database that is used to store the content such as reports and linked reports, shared data sources, report models, folders, resources, Subscription and schedule definitions, Report snapshots and report history, System properties and system-level security settings, Report execution log data, Symmetric keys and encrypted connection and credentials for report data sources, etc.
ReportServerTempDB Database – Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database. This reportservertempdb has different behavior compared to regular tempdb database. Reportservertempdb does not get recreated upon SQL Server restart, and DBAs are allowed to delete this database, in which case we need to restore this database either using available backups or database files if available.

Backup Considerations for Report Server databases – It is important to backup report server databases to be able to recover reporting services instance in case of a server crash or a disaster. It is best to choose Full recovery model for ReportServer database and perform regular full and log backups. For ReportServerTempDB, we can choose to keep it in Simple recovery model and importantly perform regular Full backups to be able to get the sachems back. If we do not have backups of these databases, then report server configuration and settings will be lost and need to be built from scratch.

Also, it is very important to backup report server encryption keys and keep them safe on some backup share, as this is required to be able to restore reportserver databases and to be able to read data from the tables. Missing encryption keys will cause the loss of credentials and other settings which are encrypted using this key and will need to build missing things from scratch again. We can backup the key from the Report Server Configuration Manager. Not having backup of report server encryption keys may result in major issues, so just be aware and careful.

For troubleshooting report server issues, we can check eventlogs and report server logs which are mostly available at \Reporting Services\LogFiles folder. We may need to some times clean up the files if old logs or dumps are not cleaned up.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Tools to Move Database O-Premise to Amazon RDS or SQL Azure

SQLServerF1

With growing popularity of cloud technologies like Amazon RDS and SQLAzure, there has been lot of interest about understanding these technologies. These cloud solutions are cost effective but are limited on many aspects, one of which is moving data or databases between on-premise SQL Server instance to Amazon RDS and SQLAzure.

Mostly DBAs use backup/restore method to move or migrate databases/data from one server to another server or between SQL instances, but when it comes to Amazon RDS and SQLAzure, Restore is not supported as they do not provide file system level access, so this method is ruled out. Then what other methods are available for us to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.

Below are some of the options which are available to move data from on-premise SQL Server database to database on Amazon RDS and SQLAzure.

Generate Scripts – Script create database, and its objects like table schemas, views, Stored Procedures, triggers, users, database roles and permissions, etc. Note that SQL Server Logins need to be created before hand to be able to map the database users to logins. This method can also be used to transfer data, but that will create huge file and can be difficult in writing to and reading from the file and makes things complicated, so better to use this method to create empty database with schemas only.

Import/Export Wizard – SQL Server Management Studion has buit-in tool which is Import/Export wizard which can be used to transfer schema and data. This creates a SSIS package to transfer the data and mostly is easy to create and implement. However there can be some issues with Identity columns, etc, so need to use it carefully and choose proper options.

BCP – For users who are comfortable with command line can opt for BCP which can be used to export data to dat file and then import that data into Amazon RDS or SQLAzure.

Sample commands:

For moving data from on-premise to dat file
BCP.exe “[HR].[dbo].[Emp]” out “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S ServrName\SQLInstName -T

For moving data from dat file to Amazon RDS
BCP.exe “[HR].[dbo].[Emp]” in “C:\AmazonRDS\BCPData\dbo.Emp.dat” -E -n -C RAW -S AmazonRDSConn -U AmazonRDSAdminUser

SQL Database Migration Wizard – Users who are comfortable using GUI tool to take care of data transfer can use SQL Database Migration Wizard which is available in CODEPLEX, which is simple to use. This can be used for both AmazonRDS and SQLAzure.

If you are confused on which tool or which of these methods to choose from, then you may ask yourself questions like do we need to move the data once and will never going to move again and if you prefer GUI method in which case you can using GUI option using SQL Database Migration Wizard or Import/Export in SSMS. If you may require to transfer data periodically, then you may choose scripts/BCP command and use SQL Agent jobs to run it when ever required.

Hopefully this answers all your questions on how to move data between on-premise SQL Server database and AmazonRDS or SQLAzure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Moving SQL Server Databases from One Server to Another Server

SQLServerF1

One of the very common task for DBAs is to move or migrate databases from one SQL Server instance to another SQL Server Instance. The moving of the databases between SQL Server instances are regularly performed due to various reasons which include databases refresh from production to UAT or Development or Test environments, migration of databases during upgrades, hardware replacements, etc. It is very important to understand the various methods available to move the databases between the SQL instance and steps required to move to safely and successfully move the databases. Also, it is important to have options to quickly and reliable way of rolling back the changes in case of any issues. Below are some of the methods and steps required for moving or migrating SQL Server databases between SQL Server instances.

Backup/Restore: This is very reliable method of moving or migrating SQL Server databases from one instance to another instance safely. This method also leaves the existing databases on old server thus the rollback is easy during the downtime window. In this method Full, DIFF and LOG backups of the databases are performed on the source SQL instance and the backup files are copied over to the destination SQL instance and restore all the backups in order with norecovery option and restore the last log backup using with recovery option which brings the database online.  It is important to plan the type of backups to be performed on which times and prepare the sequence to restore the backup files. The amount of time taken to move the databases can be long if the size of the databases is big for which the backup, copy and restore of Full backup should be performed before the downtime and during the downtime, differential or log backup can be performed which will be small in size and can be moved quickly and restored quickly which will reduce the amount of downtime. This method is most widely used method for database refresh and migrations.

Detach/Attach: This is a good method, but involves lot of risk this not widely used. In this method, the databases are detached on the source SQL instance, the .mdf, .ndf, .ldf files are copied to the destination SQL instance and attached to the destination SQL instance. This method may take less time for moving the databases compared to the Backup/Restore, but has more risk of databases being unavailable on source server for long times. Also, if the databases contain any additional files like FullText or FileStream, then those files are to be copied as well and can become complex. In this method all connections to the source database has to be disconnected before the start of the task and will only be able to connect after all the steps are completed. This method can be used for small and non-critical databases.

Logshipping/DatabaseMirroring/AlwaysON: In this method, logshipping or database mirroring or AlwaysON Availability Groups is setup before-hand between the source and destination SQL instances and during the downtime the logshipping or database mirroring is stopped and removed and final log backup is performed on source and copied over to destination SQL instance and restored with recovery option which brings the SQL instance online. This involves very less downtime and the destination server can be prepared for migration well before and can continue even if the migration is post-poned for some days. This method is commonly used for migrating critical SQL instances from lower version to higher version or for migrating critical databases from one server to another server with less downtime.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Unable to Restore System Databases Backup Due to Version Mismatch

SQLServerF1

You may receive an error as mentioned below while trying to restore a system database Master, Model or MSDB using an old backup file.

System.Data.SqlClient.SqlError: The backup of the system database on the device D:\msdb\msdb_backup.bak
cannot be restored because it was created by a different version of the server (10.50.2550) than this server (10.50.6000). (Microsoft.SqlServer.Smo)

This error occurs because, it is not allowed to restore a system databases using backup file of the same system database whose backup was performed while it was on a different build.

Example: If you have SQL Server 2012 SP2 instance and if you try to restore an System database while was of version SP1, then it will not allow you to restore as it requires the backup of same build.

In the above error, the MSDB backup was performed while the SQL Server instance was on build 10.50.2550 and they were trying to restore it on the SQL Server instance which was on build 10.50.6000. This kind of scenario happens when there are no regular backups being performed on the SQL Server instance and patches were applied to the SQL instance and some bad day the database got corrupted and now you cannot restore the backup anymore which was performed long back. Another situation when this issue happens could be that you performed backup on another SQL Server instance and using it to restore on another SQL Server instance.

The workarounds to this problem include, bring the SQL Server instance on to same build as that of the backup file, so that you could perform the system database restore. Other option is to restore the system databases on another test server which has SQL Server on same build and then extract the required data and create the same on which ever server required. You may also try to restore the database an user database and then extract the required data from it.

This problem is only specific to System databases, but not for User databases. We can perform user database backup on SQL Server 2014 SP1 and use it to restore on SQL Server 2014 RTM build.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Difference Between Native SQL Server Backups and Third-Party Backup Tools

One of the most important responsibility of SQL Server DBAs include planning and implementing proper Backup and Recovery. Although most of the times, the backups may not seem to be required, but its importance is known when there is an disaster or corruption with the database and we have to recovery as much of data as possible. Without database backups this may not be possible.

There are various methods or tools for performing SQL Server backups with each one having its own advantages and Dis-advantages. SQL Server has built-in mechanism to facilitate automating regular database backups through maintenance plans or custom jobs created on each SQL Server instance. Other methods include installing third party backup tools on central server and manage backups and restores of all SQL Servers from central repository.

Often when planning a proper backup and recovery strategy various options are considered and their advantages and disadvantages are taken into consideration and then finally the best method for their organization will be chosen.

Below are some of differences or advantages and disadvantages with SQL Server Native Backups and 3rd party backup/recovery tools.

– SQL Server Native backup and restore solution is free, meaning it comes built-in with SQL Server. But DBAs need to manually create maintenance plans or jobs on all SQL Server instances to perform backups. 3rd party backup tools are expensive and the licensing will be most often or not is for each server or instance which makes it very expensive for small organizations.

– Performing Backups through maintenance plans or jobs are considered more stable, where as the backups and restores performed by 3rd party tools cannot be fully trusted because they use their own way of performing backups and compressing the backups. 3rd party backups need to be tested very frequently to make sure that they can be recovered to avoid any surprises during disaster.

– Support for failures with the backups or restores from Microsoft is often limited to Native SQL Server backups. DBAs have to rely on the 3rd party support in case of failures with the tools.

– One of the very important advantage of 3rd party backups over native backups is managing the backups and restores from central server. For native solution, DBAs have to implement backup jobs on each SQL instance and then work on each instance when restore is required and has additional tasks like copying the backup files to the new server where restore has to be performed, etc.

– If a new SQL Server version is released, then the backup tool may not work with the new SQL Server versions, so we have to wait for the vendor to release new version of backup tool to work on newer versions of SQL Server. Native SQL backups does not have this issue as we can just create maintenance plan jobs on new SQL Server instance.

– There can be bugs with the 3rd party backup solutions so need to work with vendor and fix the problems.

– Additional management is required for installing and configuring 3rd party backup tools and applying patches to the 3rd party backup tools.

– Need to learn about the 3rd party backup tools on how they work and what options are available and will require either DBAs or SysAdmins to learn and update themselves about the backup tools.

Considering all the above aspects, one has to choose which solution works best for their environment. Most of the medium and large enterprise organizations use 3rd party backup solutions, however there are many who develop custom solution for their own environment using native SQL backup solution.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

3rd Party Backup and Restore/Recovery Tools for SQL Server

SQLServerF1

One of the most important responsibility of SQL Server Database Administrators (DBAs) is planning, implementing and testing Backups and Restore/Recovery in SQL Server. There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

SQL Server ships with database backup capabilities, but it is on server to server basis and there is no central solution where backups and restores can be managed from a central server. Thus there are various Backup, Restore and Recovery tools for SQL Server are available in the market with various features. More information regarding choosing best backup/restore tool can be found in this article

Below are some of the popular Backup and Restore/Recovery 3rd party tools for SQL Server.

NetVault LiteSpeed from Quest – LiteSpeed tool is one of the most mature, feature-rich SQL Server backup products available. It is very widely used around the world by many organizations in almost all the industries. They also provide an excellent support and regular updates to their product. It provides integration with TSM, Adjustable Backup compression ratios, backup Encryption, • Central backup repository, supports Object level restores, etc. There are some discounts of about 20-20% offered for bulk licenses. Price is on higher side, but has many good features and is a reliable product.

SQL Safe from Idera – SQL Safe is cost effective with the features provided. This tool supports TSM integration, and is capable of object-level restores, central management for backups, etc. Object level recovery is not supported with this tool.

SQL Backup and HyperBac from RedGate – RedGate has two backup/restore products which are SQL Backup and HyperBac. same licensing applies for both the products. These are highly cost effective backup products available in the market and can be bundled with other tools in your environment. There are some limitations to the tools as it does not provide TSM integration.

There are other backup/restore tools available from CommVault which is Simpana, “SQL Server Backup” from DataBK.

What does SQL Server Backups Consists of? – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Tips to Choose Best SQL Server Third-Party Backup and Restore Products or Tools

SQLServerF1

Backups in SQL Server are one of the most important responsibility of SQL Server Database Administrators (DBAs). There could be sudden disaster or corruption in which case the database needs to be restored and recovered with minimal data loss, thus it make the planning and testing of Backup and Restore process very important.

What are SQL Server Backups: – SQL Server Backups copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup. This copy of SQL Server data can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. Recovery model of the database determines what kind of Log backups can be performed on a database, thus affects the restore process and amount of data that can be recovered after a failure.

Although Backups and restores can be performed using SQL Server Management Studio (SSMS) and using T-SQL queries, but in many environments, to make the Backup and Restore process more manageable they use Third-Party Backup tools to perform backups and restores on SQL Server databases. Although there are many third-tools it is important to choose the tools which suits best for our environment.

Below are some of the parameters which can be used to determine the best Backup and Restore/Revoery tools for your environment.

Integration with TSM – One of the important part of a Backup/Testore product is its integration with the TSM which allows you to backup and restore directly to the TSM Server. Backing up directly to TSM allows saving disk space and IO bandwidth savings. Another benefit is that the direct restores can be performed on to other servers which saves time and space.

Backup Compression – Starting with SQL Server 2008, backup compression feature was introduced. However backup tools provide their own compression methods where in the backups can be further compressed and also backups are compressed on SQL Server versions which does not have this feature available.

Backup Encryption – Encryption of SQL Server Backups was introduced with SQL Server 2014, but there are many backup tools which provide custom encryption features which encrypts and stores the SQL Server backups.

Usability – Although various backup and restore tools provide variety of features, but it makes lot of difference when it comes to the ease at which DBAs can install, configure, manage and use these tools. If Backup tool installation or configuration is complicated then it will not be adopted by DBAs as they will not be comfortable using it or will have to spend lot of time on managing it.

Product Support – One of the key factor evaluated before making a decision to but the Backup or Restore tools is the level and kind of support option available for the product. Some of the tools which provide very good support often are mostly used in the organizations.

Price/Cost of the Product – Depending on the features provided the price or cost of the tools vary. This is a very important factor to decided weather to go ahead in buying the product. Over a period of time many new backup and restore tools were developed by various organizations which brought more competition this reduced the cost or price of these tools. Also various discounts are provided based on number of licenses brought and kind of servers the tools are being used in.

Degree of Restores – SQL Server provided Backups or restores to be performed at database, files or filegroup level, but the 3rd party backup and restore tools along with standard will also support object level restores, where in one can restore a particular table or stored procedure, which is more useful and helpful in Development or Test environments.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Database Restore Failing on Server on Another Network

SQLServerF1

This is one different issue I found in MSDN forum, thought to BLOG it so that it can be helpful to others as well who face this kind of issue.

SQL Server 2008 R2 database backup was performed on a server and then the backup file is copied over to a VMDK disk which is accessible to the server. Later this VMDK file is attached to another server in a different network which does not have access to the server where backup was performed. Backup file is now copied from the VMDK server into the new server and when Restore of database is attempted, then the restore runs for some time and then throws an error that the restore operation failed. Below is more detailed error message returned by the restore.

Msg 3203, Level 16, State 1, Line 1
Read on “D:\TestDB.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
Restore database is terminating abnormally.

Initially the suspect was that the backup may be corrupt, so the backup file was copied to another server directly without the use of VMDK and the restore was successful, so this narrowed down the problem to be more related to copy of the backup file from server to VMDK and from VMDK to new server, some where it is getting corrupted.

The solution to this problem was to copy the backup file by using the option /j for Xcopy command

/j option Copies files without buffering. Recommended for very large files. This parameter was added introduced in Windows Server® 2008 R2.

Once the backup file was copied using the /j option there was no more corruption related to the backup and the restore completed successfully.

Read on failed: 13(The data is invalid.) error generally occurs if the backup file is corrupted or the disk where we are trying to backup or restore is having consistency problems.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 
1 2