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

 

High Performance CPU Power Plan for SQL Server for Better Performance

SQLServerF1

It is common to determine and implement best practices while setting up new servers or while trying to identify and fix performance problems with SQL Server running on Windows Server 2008 R2 or higher. Some of these practices are effective and are meant to upgrade the hardware of the server if the server is not performing well as expected. However there are may tips and tricks which can be followed to improve the performance of the server instead of just upgrading the hardware to improve the performance. One of the common issues we generally see is high CPU usage on server running SQL Server instance.

On Windows Server 2008 or more, set the “High Performance” power plan in Control Panel from Power Options. Of course, Windows Server sets the “Adjusted” force arrangement, which empowers vitality protection by scaling the processor execution in view of current CPU use. From Intel X5500 and other last-era CPUs, the clock is throttled down to spare force (Processor P-state), and just increments when CPU use achieves a certain point. The Minimum and Maximum Processor Performance State parameters are communicated as a rate of greatest processor recurrence, with a quality in the reach 0 – 100.

In the event that a server obliges ultra-low inertness, invariant CPU recurrence, or the extremely most astounding execution levels, for example, a database servers like SQL Server, it may not be useful that the processors continue changing to lower-execution states. In that capacity, the High Performance force arrangement tops the base processor execution state at 100 percentage.

Also, it is important to keep in mind that changing this setting alone from control panel does not help, as some servers have this enabled at BIOS level, thus need to make the change at BIOS level too if enabled in BIOS. Also for VM systems, it is important to enable this at guest and host level and on BIOS on host server. Although this is a simple change, but shows greater improvement in performance of busy servers running SQL Server.

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

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

 

WSFC Cluster Quorum Vote Configuration Not Recommended for AG

SQLServerF1

When you create an AlwaysOn availability group by using the New Availability Group Wizard in Microsoft SQL Server 2012, or when you are performing failover of the AlwaysON Availability Group (AG) every time you may receive a warning message as shows below

The current WSFC cluster quorum vote configuration is not recommended for this availability group.

The above error occurs due to various reasons. and one of which is missing windows OS patch KB 2494036 on all cluster nodes part of the AlwaysON Availability Groups. Verify and apply the mentioned patch and reboot the nodes and the issue should be resolved. However if the issue still persists even after this patch is applied on all the cluster nodes then apply latest Service Pack or Cumulative Update available for SQL Server 2012 or SQL Server 2014.

Starting with Windows Server 2008, failover clustering introduced node based voting where Windows Server Failover Clustering (WSFC) uses a majority of votes to establish a quorum for determining cluster membership. Votes are assigned to nodes in the cluster or to a witness that is either a disk or a file share witness. We can use the Configure Cluster Quorum Wizard to configure the clusters quorum model. When you configure a Node Majority, Node and Disk Majority, or Node and File Share Majority quorum model, all nodes in the cluster are each assigned one vote. However there are limitations to the votes for AlwaysON Availability Groups which involve SQL Server Clustered instance. If a  primary or a secondary replica is hosted by a Windows cluster then this replica is configured for an automatic failover without a vote. If this is by design then you may ignore the warning as it still allows the initial setup of AlwaysON Availability Groups and subsequent manual failover of AlwaysON Availability Groups, but the warning will appear each time you try to perform a manual failover.

Also it is possible to configure the votes for each node and customized as per the business requirement. This functionality helps with multi-site clusters where you may want one site to have more number of votes than other sites in a disaster recovery data center. By default all nodes in the cluster have one vote but administrators can change the vote functionality in the quorum model by configuring a particular node to have 0 votes.

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 Setup or Installation Failure While Trying to Start SQL Server Services

New installation of Microsoft SQL Server 2012 standalone Express Edition instance Failed with error “Could not find the Database Engine startup handle” “Error Code 0x851A0019”. Below is the error snippet from Setup Summary log file.

Detailed results:
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name: SQL Server Database Engine Services Instance Features
Component error code: 0x851A0019
Error description: Could not find the Database Engine startup handle.

Setup failed at the very end while it was trying to bring the SQL Server services online.
Upon checking the SQL Server Errorlog, found below errors

Error: 5178, Severity: 16, State: 1.
Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Template Data\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 1048576. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.

Above error message points towards some issue with the DISK or SAN drive, so check with the storage admin. There are issues resolved by updating the Firmware and drivers of the Hardware provider. Also, there are few known issues at Operating System level, so apply all latest fixes for the Operating System. Additionally make sure disk is formatted with NTFS file format, but not as FAT32. Make sure that the disk/folders are not read-only, mapped, or compressed drives.

Windows 7 and Windows Server 2008 R2
– Install Service Pack 1 (SP1), or install the update that is described in the following article in the Microsoft Knowledge Base:
982018 An update that improves the compatibility of Windows 7 and Windows Server 2008 R2 with Advanced Format Disks is available
– Make sure that the drivers and firmware for your storage controller and other hardware components are updated. Also, make sure that the drives and firmware support large-sector drives.

Windows Vista and Windows Server 2008
– Install the hotfix that is described in the following article in the Microsoft Knowledge Base:
2553708 A hotfix rollup that improves Windows Vista and Windows Server 2008 compatibility with Advanced Format disks
– Make sure that the drivers and firmware for your storage controller and other hardware components are updated. Also, make sure that the drivers and firmware support large-sector drives.

References:
http://support.microsoft.com/kb/2510009
http://support2.microsoft.com/kb/926930

If you could not find the SQL Server Errorlog as mentioned above, then try the below general steps to see if that is helpful in resolving the issue or making some progress.

– Make sure to uninstall the failed SQL Server instance completely using add remove programs.

– Make sure you remove all file sand folders related to SQL Server from all the drives.

– Run the setup.exe by selecting “run as administrator”

– During installation when you are selecting SQL Server service accounts from account selection page, instead of using default service accounts, use domain account ot Local system account.

This is applicable on below versions of SQL Server

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 performance Tuning Frequently Asked Question and Answers (FAQ) in Interviews PART 1

Troubleshooting SQL Server performance and SQL Server performance Tuning are most important skill for a DBA which provides lot of value and differentiates between an average DBA and a very good and skilled DBA. It is very common in interviews to ask questions related to SQL Server performance troubleshooting and tuning. Proving strong fundamentals and understanding about SQL Server performance tuning will show positive opinion on interviewer and results in better chance of clearing the interview. SQL Server performance troubleshooting and tuning is very extensive topic and covers almost entire SQL Server, as every configuration change can show affect on performance.

Below are some of the commonly asked SQL Server performance tuning questions (FAQ) and their answers provided in-line.

How do you approach or troubleshoot performance problems in SQL Server?
There are different ways of troubleshooting performance issues and there is no right or wrong way of troubleshooting performance issues, it is just my way of approaching in identifying the problem and then taking corrective actions.

– First try to understand more details about the performance issues. Normally performance problems are reported by Developers or Client as per feedback from end users or some monitoring tools reporting specific performance issue on the server.
– Ask Developers or Client questions to understand what they mean by performance problems and gather mode details to pinpoint issue to a specific application or a specific functionality of the application.
– Troubleshooting approach will differ for general performance problems affecting whole application or more specific problem like slowness with specific Stored Procedure or Query. Next steps are specific to general performance troubleshooting.
– Check the overall hardware resource usage like, CPU usage on the server, Memory usage on the server, I/O usage on the server and Network usage to see if there is any spikes in any of their usage. If yes, then drill further down in that direction, if every thing looks normal, then will proceed with checking at SQL Server level.
– Check SQL Server errorlogs and eventlogs for any errors.
– Check for any blocking or heavy locking or high number of suspended sessions.
– Check wait stats to see the top waits.
– Checking if there are regular maintenance on the SQL Server like rebuilding indexes and update of statistics. If not, then will implement those which will significantly improve the performance.
– Will run DMV’s to identify Top Duration, Top CPU, Top Read or Write intensive queries and try to tune them by creating appropriate indexes or report them to developer suggesting to re-write those queries.
– Checking for SQL configuration settings like, MaxDoP, SQL Max Server Memory, Lock Pages in Memory, Instant File Initialization, Auto-Growth settings, etc.

Above steps should help in understanding the performance problem and in fixing the same.

What question do you ask Developers or Client to understand more about the performance issue?
– What kind of performance issue are you seeing, can you be more specific? It is often said that the Application is slow or website is slow.
– Are there any specific feature or a webpage of the application that is slow or is it that entire application is slow?
– Since when you started seeing performance problems?
– Have you started seeing these problems after recent upgrades to Application or SQL Server or OS and any other changes? or Were there anything changed recently on the system, like applying patches, Hardware or Application changes performed recently?
– Are you aware of any changes to the data or increase in number of users on the SQL Server recently?
– So far have you observed anything that can point in a direction where could be the problem?
– Have you checked application and web server to make sure problem does not lie there itself? How you came to conclusion that problem is with SQL Server?
– Do we have any baseline on the application performance or query performance like, how much time it used to take before and how much time it taking now?
– Have you performed any troubleshooting thus far and what are your findings, if any, so far?

It is common that Developers or Client as expected do not have answers to most of these questions and general answer is entire Application is slow.

How do you troubleshoot slowness with a specific Stored Procedure or a Query?
– First, get more details like, how much time on an average this query was taking previously(baseline)? were there any changes to the stored procedure or query recently? How often does this query runs? Does the problem only happen on this server or other servers too, meaning can this be reproduced on Test or Dev servers?
– Check if this query is being blocked by other sessions.
– Check if this query is waiting some any resource using wait stats DMV’s.
– Check if statistics are up to date for the tables and indexes used in the Stored procedure or the query.
– Check fragmentation of the objects in the Stored procedure or the query.
– Collect execution plan of the Stored Procedure and the statements inside the SP or the query. Also collect Read/Write and Time execution statistics of the query.
– Check for any missing indexes based on the execution plan, based on table or clustered index scans.
– Try and suggest to developers if the query needs to be rewritten in a better way like replacing temp variables with tempdb tables or usage of functions in where clause.

What SQL Server tools are available to troubleshooting performance problems?
– SQL Server Management Studio (SSMS), helps us to run the queries or DMV’s, We can generate execution query plans, Read/Write statistics, Time Statistics by running the query from SSMS.
– SQL Server Profiler trace can be used to identify all the queries running during the problem time and which queries are taking longer times to complete.
– SQL Server Database Tuning Adviser (DTA) is available to check for missing index and statistics recommendations.
– SQL Server DMV’s available to get lot of in-depth details about query statistics and execution plans.
– SQL Server Performance Dashboard to generate performance related reports(This needs to be installed as additional plugin)
– Starting SQL Server 2008 R2, we can use SQL Server Extended Events can be used for more granular details with less performance overhead than profiler.
– Starting SQL Server 2008 R2, we can Management Data Warehouse (MDW) for performance troubleshooting.

How can we proactively try and make sure that performance issues do not occur?
– Follow best practices related to Operating System, SAM, Network
– Follow SQL Server best practices and SQL Server configuration settings for best performance.
– Make sure there are regular maintenance jobs like smart index maintenance tasks like rebuilding or reorganizing indexes based on fragmentation, Smart Updating statistics with Full Scan.
– Using DMV’s or other monitoring tools to monitor the resource usage on server like CPU, Memory and Disk I/O.
– Monitor for occurrences of locking, blocking and suspended sessions.
– Monitor for occurrences of Waits in SQL Server using waitstats DMV’s
– Baseline SQL Server and server resource usage.

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