How to Decide If Stretch Database Can Be Used in Your Environment

SQLServerF1

Stretch Database is an interesting and popular new feature introduced with new SQL Server 2016. Stretch database helps in migrating our historical or less frequently used or archived or cold data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the organizations like reducing the storage costs, automatically archiving the old data to Azure, etc. However, stretch database also has its own limitations which make it less likely to be used as of now with SQL Server 2016 RTM release. Hopefully Microsoft comes up with significant improvements soon in service packs or with another release in another couple of years, like it has been releasing since year 2008. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Stretch Database in a SQL Server instance requires at least one table. Once we enable it at instance level, database level and table level, it then silently begins to migrate the historical data to Azure SQL Database on Microsoft cloud. If we are storing historical data in a separate table on-premise database, then we can migrate the entire table to Azure cloud. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration. However, most important question from many organizations management and DBAs or developers is how to decide if stretch database suits their environment or requirement. Below are some points to help decide if Stretch Database can help in meeting your requirements and solve the existing problems.

– Are you looking to store historical data, and do not want to get rid of very old data, as it may be still required to be accessed rarely, then stretch database will be of great help in reducing the storage and maintenance costs of the old data and still allows you to access the old data from the azure cloud.
– Are you looking for archival solution to archive old data from the frequently accessed production data, then stretch database feature is a very good solution to your problem, as you can archive old data on to Azure SQL database cloud, and best thing is you do not need to change anything in your application, as the data storage and access is taken care purely by SQL Server itself.
– If you are looking to reduce storage, compute and memory costs, then this is ideal solution.
– If you see that some tables are very large and causing issues with maintenance like backups, reindexing, stats update, etc, then archiving is best solution and stretch database will be very good solution.
– If your backup/restore are taking too long and missing SLAs, then moving old data to Azure cloud using stretch database will move old data, thus reduces the size of on=premise tables and thus reduces size of on-premise database and now the backup/restore times will be less and should meet the SLA requirements.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

New Stretch Database Feature in SQL Server 2016

SQLServerF1

Many new features gets introduced with each new version of SQL Server releases. Even with SQL Server 2016 many new features were introduced, one of which is Stretch database, which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Stretch database will help Microsoft in making many of the customers to buy Azure SQL subscription. Following are more details regarding the benefits of using this new feature Stretch database in SQL Server 2016.
cost-effective availability for cold data – Stretch database feature allows us to transfer warm or cold data dynamically from SQL Server to Microsoft Azure SQL database and this entire process is transparent to end users, application developers and DBAs. Unlike typical cold data storage, our data will be always online and available to run queries against it. We can also specify data retention timelines to keep as much of data as required on the Azure SQL database and query it. Azure SQL databases are considered as less cost options compared to on-premise servers, so this will benefit using the low cost of Azure rather than scaling expensive, on-premises storage. We can choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs and we can scale up or down as needed.

No changes required to queries or applications – This is very important part of this feature. In traditional archiving plans, there are lot of changes required from the application side, but this is greatly reduced with stretch databases. Microsoft claims that there are no changes required at all with using this feature, because the data access to cold data on Azure SQL database is handled by SQL Server internally.
Reduced on-premises data maintenance – Because we are moving old data to Azure SQL database, we will have less amount of data on our on-premise databases, which will reduce the amount of time takes for tasks like backups, Index and statistics maintenance, etc. Also, the storage requirements on-premise will be greatly reduced this maintenance or storage will be reduced and can also use the additional storage available on-premise for other databases.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

Finding Tables Suitable for Stretch Databases in SQL Server 2016

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which we run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

We can identity the tables which can be part of Stretch Database using SQL Server 2016 upgrade advisor, SQL Server 2016 upgrade advisor has different options to analyse a regular database and to analyze for Stretch Database. To get started, first download and install SQL Server 2016 upgrade advisor on a test server. Launch the SQL Server 2016 upgrade advisor and select “Scenarios” option -> choose ‘Run Stretch Database Advisor” -> Choose “Select Databases to Analyze” -> Provide SQL Instance name and make the successful connection to the SQL instance against which we want to run the analysis -> Select one or more databases from the list of databases list provided -> Run the analysis. It will take a while for the analysis to complete, after which we will be presented with a summary page of the analysis. We can save the results to HTML file or CSV file or other options as and when available.

But there are many limitations to the tables to be part of Stretch Database at this point of time. Below are some of the limitations.

– Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure SQL table that contains the migrated data.
– Insert/Delete operations are not supported and also insert is not allowed through linked servers as well.
– Views cannot be created on the stretch enabled tables. Filters on SQL Server indexes are not propagated to the remote table.
– There are many other limitations on which data types cannot be used for the tables columns, some database properties are not supported like filetables, Memory-optimized tables, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

New Revamped SQL Server 2016 Upgrade Advisor

SQLServerF1

Most of the DBAs must be familiar with SQL Server upgrade advisor, which is used to run during planning phase of upgrading to higher SQL Server versions inorder to identify any objects or features that will get affected during or after the instance or database upgrade. With every new release of SQL Server, some old features may be deprecated or retired and behavior of some features might change in new versions. So, running upgrade advisor during planning phase on a test server where the production database is restored, will give us good idea on what objects are going to break during or after upgrading to higher version of SQL Server. In previous releases of SQL Server, the Upgrade Advisor tool was made available only few days before the release of the new SQL Server version, but with SQL Server 2016, the upgrade advisor has been released way early giving options to test the instances or databases and then to proceed with testing on RC releases of the SQL Server. Although, we may be very familiar with using the upgrade advisors for SQL Server 2005, 2008 R2, 2012 and 2014, but there have been significant changes to SQL Server 2016 Upgrade Advisor in look and feel and its usage.

We should be able to download the latest version of SQL Server 2016 Upgrade advisor from official Microsoft link here.
As per Microsoft documentation “SQL Server 2016 Upgrade Advisor Preview is a standalone tool that enables users of prior versions to run a set of upgrade rules against their SQL Server database to pinpoint breaking and behavior changes and deprecated features as well as providing help with the adoption of new features such as Stretch Database.
Once we download the SQL Server 2016 Upgrade Advisor, we will find SqlAdvisor.msi in the downloaded location. Run this msi installer by choosing run as administrator and you will need to have local administrator permission on the server where you are installing this tool. It is always recommended to install Upgrade Advisor on a test server and restore a copy of production database on the test server for analysis.

Once the installation of SQL Server 2016 upgrade advisor is completed successfully, launch the program and you will first see a What’s New screen which has some information regarding What’s new in SQL Server 2016 Upgrade Advisor. After that we will find a title bar with SQL Server 2016 Upgrade Advisor and next to it, you will see two main options “Scenarios” and “Active”. Apart from these two options, there are Notifications, Send Feedback and Settings which allows us to see new notifications for the product and to send any feedback to Microsoft for any bugs or new feature requests. Settings option has a gear icon which is used to choose any settings, as of now there is not much you can do here.

Inorder to start the upgrade analysis process, we need to choose “Scenarios” menu -> Run Database Upgrade Advisor -> Select databases to analyze -> Select Instance -> Select the instance from the drop down list if any available or create a new connection -> If we need to specify port number as well inorder to connect to the instance, then we can specify the port number by clicking on advanced options -> Once we successfully made connection to the SQL Server instance, next we will see list of databases to choose for the upgrade analysis -> Select one or more databases based on your requirement -> Run the upgrade analysis by clicking on Run button. It will take a while for the analysis to be performed. Once analysis is complete, you will be presented with a summary page. We will not only see the analysis for SQL Server 2016 instance, but we will find it for other versions as well based on what is the SQL Server version where upgrade advisor was run against, like if you run it against SQL Server 2008 R2 instance, then you will see the analysis for SQL Server 2012, 2014 and 2016. We can drill down for each of the versions and see the affected objects and the details about the impact and recommendation.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

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

 

CPU Affinity Settings on Server with Multiple SQL Server Instances

SQLServerF1

It is common to see multiple SQL Server instances being installed on one server. The intention behind this is to reduce the licensing cost related to Operating System and also to use the hardware resources effectively. Also, having multiple instances separate maintenance and provides more security from not allowing users of other applications to connect to different SQL Server instance databases. With introduction of visualization, the reason for having multiple SQL Server instances has reduced a bit by allocating on VM for each SQL Server instance thus avoiding issues with maintenance of OS or hardware. However, still there are older systems or some newer systems too which still use multiple SQL Server instances on one server.

There are some disadvantages of running multiple SQL Server instances on one server like, if built-in administrators group is added to SQL Server instance as sysadmin, then all local administrators get access to SQL Server instance by default which can be security issue, irrespective of it, a local administrator can gain access to SQL Server instance without being having permissions to the instance through backdoor by starting SQL Server instance in single user mode. Also, there can be performance issues to all SQL Server instances, if one of the SQL Server instance starts to behave bad due to bad queries, improper index and stats maintenance or bad application design.

One of the performance issue you notice on servers is high CPU usage causing performance issues. There can be one of more instances consuming more CPU thus starving other SQL Server instances, thus overall server performance degrades and application users will start to complain. One of the ways to avoid each SQL Server instance consuming more CPU and causing contention is to allocate each SQL Server instance specific CPUs available on the server. For example, if a server has 16 processors and if there are 4 SQL Server instances running on the box, we could analyse CPU requirements for each SQL Server instance based on number of databases, size of databases, number of concurrent users and max CPU required during high load, and then assign specific number of CPUs to each instance. So, for one instance if it requires more CPU usage, then allocate 8 processors to user and for remaining 3 instances use assign remaining available processors based on the usage. If all SQL Server instances usage requirement is greater than available processors, then we will need to plan to move one or more SQL Server instances on to another server.

We can use sp_configure of from instance properties, choose which CPUs to be allocated for which SQL Server instances. There is also option available to choose IO affinity mask, so do not change it, instead just change the affinity mask or CPU affinity mask. There can also be affinity 64 settings, which you can ignore and just use affinity setting or CPU affinity setting. Do not change IO affinity setting. For each SQL Server instance choose different CPUs to avoid overlapping.

Also, it is good to consider reviewing and adjusting the Max Degree of Parallelism (MaxDoP) setting as per best practices and the allocated number of CPUs for each SQL Server instance.

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

 

Tips for DBAs to Troubleshooting SQL Server Performance Issues

SQLServerF1

Troubleshooting SQL Server performance issues is one of the common and important task of DBAs. It is not very easy to troubleshoot performance issues without prior experience or good knowledge about how SQL Server query execution works and what factors can contribute to performance issues with the SQL Server instances. It is very common to see many entry level DBAs or even DBAs with more than five years of experience still struggle dealing with performance issues, as there are just many things that can cause the performance problems and many symptoms may be interrelated and not easy to diagnose and come to a conclusion after which the solution to be found and implemented. In many organizations troubleshooting performance problems take many hours to days and even weeks too.

To help DBAs to be able to find a quick list of things which they can check or look into in case any SQL Server performance issues are related. It is important to note that troubleshooting performance issues with SQL Server is very broad ranging from SQL Server configuration, OS configuration, Network or other hardware configuration, blocking, waits, fragmentation, outdated statistics, etc. Below are some of the things which DBAs can look into when performance issues are reported with SQL Server instances.

– Understand about the problem. Generally performance issues are reported by developers, Managers, application users or some monitoring tools. Depending on the source of the issue reported, gather data by asking questions like what do their interpretation of performance issues mean, why do they think SQL Server is cause of performance issues, what data they have collected, what symptoms do they see with application, did they collect any data so far, etc. Collecting as much data can be helpful to move in the right direction.

– If the performance issues are reported by all users, that means it is general slowness, can be anything from Hardware configuration issues to SQL Server query problems. We can start looking into basic SQL Server configuration settings like MaxDoP, Max Server Memory, SQL Server version and patch details, platform, etc. Gather OS configuration details like CPU, Memory and disk details and if they are as per best practices like data and log files on different disks, tempdb on its dedicated disk, etc. Also, sometimes OS settings related to instant file initialization, lock pages in memory, CPU power plan set to balanced can show impact too.

– Quickly check on perform to see if there are any resource bottleneck. Look into counters for CPU, Memory, Disk IO and network to see any issues or any values which are outside of expected values.

– From SQL Server, we can check if there are regular maintenance like rebuild of indexes and statistics updates. Check for fragmentation and when stats were last updated. Check if there are any jobs currently running, if jobs related to reindexing, stats update, checkdb or backups running, then stop them. Check if there is any blocking happening on the SQL Server instance, if found, check the headblocker and take appropriate action.

– Check wait stats on the server and see the top contributing wait and see if we can related to any physical resource bottleneck.

– Check currently running sessions which are taking more time and see why they are taking more time. Try tune them by creating any missing indexes or rewrite queries, etc.

– If the application is related to Vendor, then check for any best practices from Vendor for improving the performance.

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