Limitations of Amazon RDS SQL Server 2012

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. Some of the important restricted features are mentioned below.

– Only SQL Authentication Logins are allowed. Windows authentication is not supported, so applications are to be changed to only use SQL Server authentication.
– Amazon RDS only provides support for core database engine of SQL Server, but does not include SSRS, SSAS, SSIS, etc. If application requires these services, then they can be implemented on on premise server and can access data from Amazon RDS.
– Maintenance plans are not supported, so we would need to use SQL Scripts to implement the maintenance tasks like backups, Index and statistics maintenance, Integrity checks, etc. There are already many of these scripts available like Ola Hellengren scripts. Using scripts also provides flexibility of dealing with these maintenance tasks.

– Linked Server access is limited and only can be configure and used from Amazon RDS to outside SQL Server instances over internet or other Amazon RDS instances.
– SQL Agent role is limited, which causes issues where one user with SQL Agent role cannot see jobs created by another user with SQL Agent role. So workaround is to use only one account for job management.
– In Amazon RDS, during the initial creation of instance, we choose the storage and this storage cannot be changed later. There are no workarounds and will need to create new instance with required storage design and then have to migrate data from old instance to new instance.
– Replication can be setup, but is is limited. We can only configure Amazon RDS instance as a subscriber. We cannot use Amazon RDS instance as publisher/distributor. We cannot use pull subscription too.

Some of the other features which are not supported by Amazon RDS SQL Server 2012 or lower include below.

Database Mail
Service Broker
Database Log Shipping
Change Data Capture (CDC) – Consider using Change Tracking as an alternative to CDC.
Additional T-SQL endpoints
Performance Data Collector
Distribution Transaction Coordinator (MSDTC)
WCF Data Services
FILESTREAM support
Policy-Based Management
SQL Server Audit
BULK INSERT and OPENROWSET(BULK…) features. These must be run from client-based server storage.
Data Quality Services
Instant file initialization
Always On (2012 Enterprise Edition)
File tables
Server level triggers

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

 

Infinite Recompile Warning Messages in SQL Server Errorlog

SQLServerF1

Sometimes you may notice below warning messages in SQL Server Errorlog
A possible infinite recompile was detected for SQLHANDLE 0x020000006E8C161EA193363B55EBDF5A21857C439998726E, PlanHandle 0x06000B006E8C161E40017CDE0A0000000000000000000000, starting offset 0, ending offset -1. The last recompile reason was 6.
It is not so eay to troubleshoot this error as this may happen due to various different reasons and sometimes it may be intermittent and gets resolved itself or sometimes it happens continuously until fixed. These messages sometimes may also be logged due to changes to statistics causing recompile and can be ignored.

This message is logged to SQL Server errorlog if one or more SQL statements caused the query batch to recompile at least 50 times or more. The specified statement should be corrected to avoid further recompilations. In the warning message you will find the SQLHandle and planhandle which can be used to find the query or batch that is causing this warning message to be logged to SQL errorlog. Also, there is another important piece of information mentioned in the warning message, which is “The last recompile reason was %d”. Below are the possible recompile reason code and associated description.

Reason code

Description

1

Schema changed

2

Statistics changed

3

Deferred compile

4

Set option changed

5

Temp table changed

6

Remote rowset changed

7

For Browse permissions changed

8

Query notification environment changed

9

Partition view changed

10

Cursor options changed

11

Option (recompile) requested

The most common recompile reason is the recompile code 2. From the table above, we can see that this occurs due to statistics-based recompile. If statistics on a table accessed by a SQL query or batch changes, a recompile for the query will be triggered, and if they change often enough, then you could legitimately see this warning in the error log. Therefore, if you see the infinite recompile message appear in the errorlog with reason equal to 2, and you know the statistics on your tables are likely changing often (because the doing a lot of data modification), you can simply ignore the warning.

If the recompile reason was 6, it will be related to remote rows changing on linked server. If this warning message appears continuously, then to resolve this, you may need to look into code and take appropriate action. Some of the possible resolutions for this include selecting the remote rowset into a local temporary table before joining with the remote data, this way the statistics will be available, locally. This may also lead to performance issues that you may be observing in your environment, so bringing locally may also resolves the performance issue we are experiencing.

The other recompile messages are not so often and are self explanatory. You can find the SQL batch or statement causing the infinite recompile by running below query and passing plan_handle, starting_offset and ending_offset which you can find in the warning message logged.

SELECT DB_NAME(st.dbid) AS database_name
, OBJECT_NAME(st.objectid) AS object_name
, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text (sql_handle) AS st
WHERE qs.statement_start_offset = starting_offset
AND qs.statement_end_offset = ending_offset
AND qs.plan_handle = plan_handle;

Once you know the statement, you can now check the code to make the required adjustments to avoid this message from occurring.

Hope this was helpful.

This is applicable for below versions of SQL Server

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