There are several advanced options that you can modify in SQL Server. If you want to turn on optimize for ad hoc workloads in SQL Server, you can run the following script
sql EXEC sys.sp_configure N’optimize for ad hoc workloads’, N’1’ RECONFIGURE WITH OVERRIDE GO
If you want to use OPENROWSET, you can run the following sql EXECUTE sys.sp_configure'Ad Hoc Distributed Queries', '1' RECONFIGURE WITH OVERRIDE GO
When running SQL Server on Amazon’s AWS RDS, you can’t do it like that. If you try running it, you will get the following error.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Line 1
You do not have permission to run the RECONFIGURE statement.
Here is how you have to do it. From the dashboard click on DB Parameter Groups. Click on Create DB Parameter Group, pick the version of SQL Server and fill in the text boxes. Here is what it looks like
Check the DB Parameter Group you just created and click on Edit Parameters
You will see a screen like this pop up
Modify the ones that you are interested in and click Save Changes
Click on DB Instances, Click on the Instance Actions dropdown
Select Modify from this dropdown
You will see a screen like this
In the Parameter Group dropdown, pick the Parameter Group you just created, hit Continue and then Modify DB Instance.
In order for this to take effect, you need to reboot your instance
Click on DB Instances, click on the Instance Actions dropdown
Select reboot from this dropdown
Once the instance is available again, connect from SSMS, right click on the Server, select Properties and click on the Advanced page. It should now match what you have edited in the DB Parameters Group
That is it for this post, as you can see it is pretty simple to do
Also check out my other AWS RDS SQL Server posts: Trying out Amazon Relational Database Service and How to read the error log on an Amazon RDS SQL Server instance