SQL Server High CPU Issues Due to Edition Limitations

SQLServerF1

There are variety of performance issues that can cause SQL Server performance issues. Performances can range from low server Hardware configuration, improper OS or SQL Server settings, fragmentation, blocking, no proper maintenance like index rebuilds or stats updates, etc. It is not easy to identify or pin point what is causing the slowness or performance problems with the SQL Server. As a DBAs we will need to review everything related to the hardware, OS and SQL Server to able to find the root cause of the issue.

There are many cases where performance issues are caused due to low server hardware configuration like lack of CPU power, or insufficient memory available or under-performing disk IO. We can look at various parameters like perfmon, SQL Server dmv statistics, query waits, etc to be able to understand if there is real bottleneck with the hardware and if we need to upgrade the hardware for better performance. However there can be situations, where the server although has a good hardware configuration interms of CPU, Memory or Disks, improper settings on some limitations cause the SQL Server not to use all of the available hardware on the server.

One of such issue related to the slowness caused by CPU usage due to limitations on the SQL Server edition been used. On one of our client server, there were multiple SQL Server instances installed. There was a monitoring tool which used to monitor the server and used to raise intermittent alerts for high CPU usage. Upon connecting to the server and verifying, the CPU usage was only reported as 50% usage on the server. Users also used to report about the performance issues some times, but not frequently.

Upon checking the server, from task manager the CPU usage was reported around 50% and this server has 8 sockets with one core each, The server has Windows Server 2012 as the Operating System and the task manager, by default combines all the processors available on the server and shows the average usage of all the processors. There is also an option to see the detailed view of CPU usage of each individual processor usage, checking which we found that only four processors are pegged to 100%, where as rest 4 CPUs were having negligible usage. So, it appeared that the SQL Server instances were using only 4 CPUs thus the high CPU usage with the four CPUs only.

Upon checking further, we noticed that all the SQL Server instances installed on the server were Standard Edition. Checking the documentation showed that SQL Server standard edition can use maximum of 4 sockets or 16 cores, which ever is less. Similar limitations on other SQL Server 2008 R2 or SQL Server 2005 versions of 4 sockets only.
https://msdn.microsoft.com/en-us/library/ms143760(v=sql.110).aspx

We can also check and confirm this on SQL Server 2012 instances by checking the SQL errorlog which will have a message like “SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing”.

For SQL Server 2008 R2 or SQL Server 2005, we can query sys.dm_os_schedulers to see how many processors are been used by the SQL Server instances.

The server where we had issues had multiple SQL Server instances, so we had to use processor affinity mask settings and map each instance to use first 4 or last 4 processors allowed us to use all the available processors on the server, thus reducing the CPU contention.

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

 

Blocking Due to SPID in Sleeping State in SQL Server

SQLServerF1

Blocking is a common issues noticed in any of any relational database management system (RDBMS) which uses lock-based concurrency. In SQL Server, blocking generally occurs when one SPID holds a lock on a specific resource and a another SPID attempts to acquire a conflicting lock type on the same resource. Generally, the time frame which the first SPID holds locks on the resource is very small, so when it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and happens many a times throughout with no noticeable effect on system performance.

It is common for DBAs to notice blocking on various servers and typically the locks held for a short time does not cause any issues, nor is identified by any monitoring tool. Sometimes, blocking can get severe and cause performance issues from timeouts to slowness, etc. DBAs generally deal with blocking issues by checking on the head or lead blocker causing the blocking and try to understand why the head blocker is taking long time to complete and it is not very important, then DBAs kill the SPID. If the blocking issue is persistent then DBAs identify the culprit and tune it by adding indexes or rewriting the query.

However, sometimes there can be strange blocking issues, one of such issue is blocking caused by a SPID which is in sleeping state. In general, a SPID which finishes its task will leave any locks held, so the sleeping sessions are not expected to cause any blocking issues, however in some environments, persistent blocking issues are noticed due to SPID which is in sleeping state still holds the locks and does not release it until the connection is closed from the application of till the SPID is killed by the DBAs. If we try to understand why the blocking occurs with a session in sleeping state, there could be different reasons and some of the are mentioned below.

– Mostly this issue happens when someone uses SQL Server Management Studio(SSMS) query analyzer window, runs some bad queries and leave the session open. The queries which were run either have a explicit open transaction or an implicit transaction which holds locks and does not release the locks because no commit or rollback was issued. We need to identify such sessions, the user who is running queries from that session, what queries are run, what locks are held, from which application are the session from, hostname, etc and then DBAs can go ahead and kill the sessions. Then DBAs need to contact the respective user or management and report the issue to avoid happening in future.

– This can also happen when an application opens a connection with SQL Server, runs some query and then due to some issues, the application connection is lost either due to application crash or hang or some application problems. No signal is sent to SQL Server due to sudden crash, so SQL Server still assumes that the application may still send additional queries and keeps the session available. This is due to bad application design and needs to fix the application code.

– This can also happen if application has bad code, which opens transactions and fails to commit or rollback in case of query failures or other issues.

– Applications also uses session pooling, where is sessions are reused when required. Periodically applications need to close sessions which have not been reused after sometime.

– We can also suggest developer to use SET XACT_ABORT ON for the connection, or in any stored procedures which begin transactions and are not cleaning up following an error. In the event of a run-time error, this setting will abort any open transactions and return control to the client. It is important to note that T-SQL statements following the statement which caused the error will not be executed.

– Some older or incompatible client components may cause these kind of issues too, so need to keep them updated and use latest and compatible connectivity components.

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

 

Introduction to SQLAzure for SQL Server DBAs

SQLServerF1

Microsoft has been aggressively focussing on promoting cloud technology. As a SQL Server DBA, one might wonder what is all about this cloud and how this works and how this impacts their role as a DBA. Although the terminology may be different, but a lot about cloud may already known to you or worked on, but may be not able to correlate to what you already know. For SQL Server Microsoft is offering two cloud platforms, one of which is Infrastructure as a service and the other one is database as a service.

Infrastructure as a service – This is similar like a hyper-v or VMWare VM, where you have a VM machine on which you install and manage SQL Server instances, databases and jobs, etc. The only difference is the VMWARE or Hyper-V management is taken care by your client company or your company, where in case of Microsoft Azure cloud the infrastructure is taken care by Microsoft and as a DBA, we need to take care of installing SQL Server, patching, maintenance, administration similar to what we used to do, but just on a Microsoft data centre.

Database as a service – This might be something different and which many DBAs may not be familiar about. In this the OS, Network, Storage and also SQL Server installation, patching, Backups, etc are taken care by Microsoft and we as DBAs are not responsible for such tasks, instead we can focus on other tasks such as migrating data from on-premise SQL Server to SQL Azure cloud and checking and working on making the SQL Server database resilient for performance. Database as a service is not a regular SQL Server instance which we used to manage on-premise, instead there are lot of limitations on what we can do and what changes we can make, for example, we cannot change many instance settings directly, we cannot alter storage configuration, or tempdb configuration, etc, instead we focus more at the database level and at data level.

If you are wondering what are the advantages for choosing SQLZure cloud solution is mainly reduction in cost of maintaining hardware and SQL Server maintenance of patches/backups, etc. Depending on the criticality of our database server and application, we can choose different kind of licensing based on our requirement of performance, data recovery and Disaster recovery features. So, for small test/dev servers you can choose servers with basic or minimal cost configuration and for productions servers depending on the size and usage of the database we can choose appropriate license. Different types of licensing includes, Basic, Standard and Premium and these are sub divided into S0-S1-S2-S3 for Standard and P1-P2-P3 for Premium.

As our data is stored on cloud on third party vendor place, it is important to understand how quickly we can recover our data and how much of data loss can occur in case there is a disaster, for which there are different types of Disaster recovery strategies to choose from, which include Geo-Restore, Standard Geo-Replication, Active Geo-Replication. In Geo-Restore, a copy of database can be restored on another region, but this will have data older than 24 hours, thus may not be good option for production data, instead can be used for dev. In Standard Geo-Replication, we can recover data up to 30 minutes before crash and can take up to 2 hours for the restore to complete and database to be available, but this will increase the cost. Finally, Active Geo-Replication, the data loss could be reduced to 5 minutes and amount of time to restore would about about an hour, this further increases the cost. Depending on our requirement, we can choose the best solution that suits.

in SQLZure, there is something known as DTU(Database Throughput Unit), which is used to measure the performance we get out of our SQLZure instance. DTU is a combined measure of CPU, Memory and IO of a database on a server and we can use this to compare the performance of database between different servers.

Hope this was helpful.

This is applicable for below versions of SQL Server

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

 

Best Practices to run SQL Server on Amazon RDS

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although running SQL Server on Amazon reduces cost to great affect, but has its own limitations too and need to review them carefully before moving data to Amazon RDS.

– Always Better to choose Multi-Availability zone deployment to have high availability, as we never know when the instance may go down, so to avoid or reduce the amount of down time, having high availability using Multi-Availability zone deployment will be helpful.
– Plan thoroughly in advance about the CPU, Memory and Storage requirements, changing these later may be complex. Storage cannot be changed once instance is created.
– Choose the proper server level settings,as they can be modified using Parameter Group and can be some what difficult to change and may be required instance to be restarted for few settings changes to apply.
– Patch levels are often controlled by Amazon RDS and may have some known issues due to older version of patches available and can impact your application due to known issues.
– Always choose the database files with proper initial size and auto-growth at higher sizes to avoid growing of files during application usage, this reducing the time and improving performance.
– If you are using Multi-Availability zone deployment, note the amount of time taken for the synchronization, as this is synchronous and thus the time for sync can have significant impact on you data access from application.

There may be other best practices, and following them will help running your databases on Amazon RDS seemless and provide cost effective solution replacing on-premise SQL Server databases. Not considering these limitations can significantly cause timeouts or performance issues and can make you feel frustrated for moving to Amazon RDS. Proper planning and understanding if your application works well on Amazon RDS will decide how the application will behave in future. Also, as time the amount of data can increase and can have significant impact on performance, so it is important to consider server configuration for future data growth and user usage.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

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

 

DBA Tasks Offloaded After Moving to AmazonRDS or SQLAzure

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

For On-Premise SQL Server instances, DBAs and System Admins are responsible for many tasks which may be day to day or some scheduled tasks like patching, maintenance etc. Amazon RDS or SQL Azure can offload many such tasks which SQL Server DBA or System Admin has to take care and thus can reduce the load on the DBAs on large environments with many servers and applications. Instead of spending time on repeated things, they can focus more on other important DBA activities like performance turning, health checks, etc.

Some of the important activities or tasks offloaded from DBAs or System Admins are listed below. These are not the only ones, but there are others too.
Patching – Patching of SQL Server instances or Operating System, drivers, etc have been one of the core responsibilities of the DBAs or System Administrators and often are repeatable and does not require smart work instead requires some hard work on working during off hours to reduce the impact on the users using the application. With Amazon RDS, these patching is taken care by Amazon team and we only have to specify the window on when the patching needs to take place. However this limits the control on what patches can be applied, as every patch we want will not be applied, as they need to certify which patch needs to be applied and tested to work smoothly.

Upgrade to Higher Version – Upgrading on-premise SQL Server is considered one of the major project and requires lot of planning and time to implement and test. With Amazon RDS, the implementation of upgrade and any issues are taken care by Amazon and we can just focus on testing our application and fixing the appliation compatibility issues.

Backups – Backups are taken care by Amazon and we just need to specify the time when backups can be performed and how many days backups are required to be retained. Point to note is that the number of backups to be retained is directly proportional to the increase in storage price to keep the backups. By default recovery model used in SIMPLE and cannot be changed to simple, instead we need to choose retention period as 0, meaning we do not need any backups. Thus, for databases which need backups will have full and transaction log backups and mostly we can recover our data to as early as 5 minutes before the crash.

High Availability – Amazon provides support for High Availability and it is important to choose the high availability feature, as this is third party vendor and on data centre which we have no control or details about, so we never know if there will be any issues or outages. For high availability, RDS can automatically setup a database mirroring secondary on another Availability Zone often in another Amazon data centre in the same region.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2
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

 

AmazonRDS SQL Server DB Instance Settings Which Can be Modified

SQLServerF1

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

AmazonRDS SQL Server DB Instance has various settings which can be modified to suit our requirements. Below are some of the important setting which can be modified after the AmazonRDS SQL Server DB Instance has been created. It is important to understand that modifying these settings can have adverse effect too, so just be careful and fully understand the change which you are making. You can make all these changes listed below from AmazonRDS web console.

DB Engine Version – Used to choose the version of the SQL Server database engine that we want to use.
Multi-AZ Deployment – Allows us to create a standby mirror of our DB instance in another Availability Zone, click Yes, otherwise, click No.
DB Instance Identifier – We can rename our DB instance by providing a new name. When we change the DB instance identifier, instance reboot will occur immediately if we set Apply Immediately to true, or will occur during the next maintenance window if we set Apply Immediately to false.
New Master Password – We can change the master password. By resetting the master password, we also reset permissions for the DB instance.
Security Group – We can choose the security group we want to associate with the Database Instance.

Certificate Authority – We can choose a certificate we want to use.
Publicly Accessible – Selecting Yes allows the DB instance a public IP address, meaning that it will be accessible outside the VPC (the DB instance also needs to be in a public subnet in the VPC), Id we choose No, the the DB instance will only be accessible from inside the VPC.
Parameter Group – Allows us to choose the parameter group we want to associate with the database instance. Changing this setting does not result in an outage. The parameter group name itself is changed immediately, but the actual parameter changes are not applied until you reboot the instance without failover.
Option Group – We can choose the option group which we want to associate with out database instance.
Database Port – We can specify a particular port to use for our Database instance.
Backup Retention Period – We can adjust or modify the database backup retention period. To disable automatic backups, we can set this value to 0(useful for test environments to reduce cost).
Backup Window – We can choose the backup windows, on when the backup of the database needs to be performed. This time is in Universal Coordinated Time (UTC) and a duration in hours.
Auto Minor Version Upgrade – If you want your DB instance to receive minor engine version upgrades automatically when they become available, click Yes. Upgrades are installed only during your scheduled maintenance window.
Maintenance Window – We can set the time range during which system maintenance, including patchs, upgrades, will occur. This is start time in UTC and a duration in hours.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

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

 

How to Connect to AmazonRDS from SQL Server Management Studio?

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. Although Amazon RDS is great for many applications to store its data, but has many limitations where many features cannot be used due to restricted permissions at file system level and on registry and OS level.

Although application connect to the database and works fine, it is important for DBAs or Developers to be able to query for data and to manage Databases and other objects places on AmazonRDS. There may be different ways to connect to Amazon RDS and manage or query data, but one of the easiest method and most popular and convenient way is to do it through well known tool which is SQL Server Management Studio(SSMS). We only need few basic details and then we should be good to connect to our database on AmazonRDS.

Firstly we need to create a security group from AmazonRDS web console, and then we must modify the DB instance to associate it with the security group. Once we are done with it, we can follow below steps to be able to connect to our database from SSMS

– On the Instances page of the AWS Management Console, we need to choose the arrow next to the DB instance to show the instance details. Make a note of the server name and port of the DB instance, which are displayed in the Endpoint field at the top of the panel, and the master user name, which is displayed in the Username field in the Configuration Details section.

– Launch Microsoft SQL Server Management Studio(SSMS). Then Connect to Server dialog box appears in the Server section “select Database Engine”. Now provide the server name of the DB instance then a comma(,) and port number. Example: EndPoint,Port

– Now choose SQL Server as authentication type as this is the only authentication method supported in AmazonDRS. Provide the user name and password and then click connect and you should be connected successfully, unless there are any port or firewall issues. Open a new query window and run few sample queries to make sure you can run queries.

Some of the known issues which you may face while trying to connect to AmazonRDS from SQL Server Management Studio are related to to your local firewall and the IP addresses you authorized to access your DB instance in the instance’s security group are not in sync. If you cannot send out or receive communications over the port you specified when you created the DB instance, you will not be able to connect to the DB instance. Check with your network administrator to determine if the port you specified for your DB instance is allowed to be used for inbound and outbound communication.or newly created DB instances, you must wait for the DB instance status to be “Available” before you can connect to the instance. Depending on the size of your DB instance, it can take up to 20 minutes before the instance is available.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2012
SQL Server 2008 R2

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

 
1 2 3 4 5 558