Amazon SQL Server Configuration

Post date: Sep 21, 2012 4:50:17 PM

Background

This post serves as a placeholder for information related to configuration and continual maintenance for SQL Servers in the EC2 Environment.

Configuring New Database On Amazon SQL Server

Please ensure that you consult Kevin Foley whenever creating a new database in ANY Amazon environment when taking the steps below.

Overview

The new pattern is to allocate and dedicate a single drive for a given database. This is accomplished by:

• • Creating the drive/volume in Amazon and adding to the EC2 instance

  • Configuring drive in windows instance using disk manager

  • Restoring database to new drive

Step 1 - Creating the volume in Amazon

From EC2->ELASTIC BLOCK STORE->Volumes

• • Click "Create Volume" button

  • Choose Size. 100 GB seems to be popular

  • Ensure Availability Zone is the same zone as the EC2 instance you wish to add volume to.

  • No Snapshot - This is an empty drive

  • Volume Type-> Choose "Standard". Note Provisioned IOPS is a SSD drive

  • Choose EC2 SQL Server instance

Step 2 - Configuring new drive in Windows

From RDP session on SQL Server EC2 Instance

• • Start->Computer->Right Click->Manage

  • Server Manager->Disk Management

  • Note new Disk will appear as "Disk {x}" and shows as "Unallocated". If Disk is offline, Right click and choose "Online"

  • Right click Disk Section->New Simple Volume

  • Choose default size->Next

  • Assign a drive letter that is unused (Eg. "G")

  • Format as NTFS. Ensure you assign a volume label that is representative of the new database name (eg. Stratus). Ensure "Perform a quick format" is checked and "Enable file and folder compression" is NOT checked

Note: if a newly allocated disk is shown offline, and the context menu has the 'Online' option disabled, follow these disk partitioning instructions.

Step 3 - Restoring database to new drive

Create Folders for target file groups

From new drive, create folders in root. These will be used in restore script.

• • SQLBlobs

  • SQLData

  • SQLIndexes

  • SQLPrimary

  • SQLTranlog

Execute Restore Script

This script will create a new database on DBMS from restoring a backup and maps filegroups to new drive. It will also rename file groups to reflect new database name.

Download Database.Restore.sql

This script will need to be run separately in 3 steps. Note the steps are differentiated by comments.

• • Ensure database name reflects new target database

  • Ensure the drive references in steps 1 match the new drive path

  • Ensure the new name references in step 3 match the new database name

Run script by highlight steps and running each step at a time

Optional steps configuring OPENROWSET functionality to Excel/Office files in SQL 2008/12 64bit

Install the Microsoft Access Database Engine 2010 Redistributable, hotfix and service pack update. 

Please note that the hotfix must be installed BEFORE the service pack.

All items should be installed via "run as admin".

All files can be found in SVN under /Trunk/DataCenter/SQL

1) Install the engine: AccessDatabaseEngine_x64.exe

2) Verify obsolete MSO.DLL version is  14.0.4760.1000 as found in the folder below

C:\Program Files\Common Files\Microsoft Shared\OFFICE14 

3) Install the hotfix: office2010-kb2516475-fullfile-x64-glb.exe

Verify new MSO.DLL version is  14.0.5137.500 ( properties / details )

4) Install the service pack : accessdatabaseengine2010sp1-kb2460011-x64-fullfile-en-us.exe

Verify MSO.DLL version is still 14.0.5137.500 is retained.

REBOOT

Update SQL via SSMS 

USE [master]

GO

sp_configure 'show advanced option', 1

reconfigure

GO

sp_configure 'Ad Hoc Distributed Queries', 1

reconfigure

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

-- The two statements below are for future use if we ever want to insert into an existing XLS file

-- None of our servers currently have this functionality and it has not been tested.

/*

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

GO

*/

Update registry via REGEDIT and add the key below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server 

\MSSQL10_50.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0\DisallowAdhocAccess 

Where DisallowAdhocAccess  is a REG_DWORD set to 0

Please note that the path "\MSSQL10_50.MSSQLSERVER\" will change by SQL version.

You can find the exact path by locating the "AllowInProcess" keys that were created by the above script.

Verify that the query works.

Sample query

SELECT * 

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=D:\Documents\www.valuationmodule.com\NewOrderBatch\Sample.xls', 

'SELECT * FROM [Sheet1$]')

REBOOT and verify the query still works.