SQL Server on Amazon RDS – Create Database – File Groups

Background

Quick commentary on creating a database that sits on multiple filegroups on Amazon RDS.

 

Guide

Launch SQL Server Management Studio (SSMS) and connect to your Amazon RDS MS SQL Server.

New Database

General

Initial

Here we specify the database name has DBLAB.

And, the system autofills the Logical names of DBLAB for the File Type of ROW.  And, DBLAB_log for the LOG.

NewDatabase - General

 

Filegroups

Initial

Here is the initial screen for filegroups.

NewDatabase-FileGroups

 

Post

Here is what things look like once we added Indexes and Indexes2 as file groups.

NewDatabase-FileGroups-AddedAdditionalFilegroups

General

Post

We return to the general Tab and added new files and positioning them to the corresponding Filegroup

  1. Logical name of DBLAB_Indexes attached to Indexes Filegroup
  2. Logical name of DBLAB_Indexes2 attached to Indexes2 Filegroup

 

NewDatabase - General-AddedAdditionalFiles

 

Best Practice

Best practice suggest that we should target a separate physical disk, LUN, for each file group.

If we try to seek out an alternate File path, we will get the error stated below.

Image

xp_fixeddrives-error-229

Textual


The execute permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'

Implication

We do not have permission to access xp_fixedrives.  xp_fixedrives lists available storage on attached physical drives.

SQL Script

Btw, here is the SQL Script.


CREATE DATABASE [DBLAB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DBLAB', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB.mdf' , SIZE = 5120KB , FILEGROWTH = 10%), 
 FILEGROUP [Indexes] 
( NAME = N'DBLAB_Indexes', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB ), 
 FILEGROUP [Indexes2] 
( NAME = N'DBLAB_Indexes2', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_Indexes2.ndf' , SIZE = 5120KB , FILEGROWTH = 204800KB )
 LOG ON 
( NAME = N'DBLAB_log', FILENAME = N'D:\RDSDBDATA\DATA\DBLAB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO


Summary

One has to rely on the underlying storage configuration when using a provisioned system such as RDS.

Typical engineering thoughts such as separating data and logs are not available.

Same with attempts to disambiguate data and index I/O.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s