Quick commentary on creating a database that sits on multiple filegroups on Amazon RDS.
Launch SQL Server Management Studio (SSMS) and connect to your Amazon RDS MS SQL Server.
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.
Here is the initial screen for filegroups.
Here is what things look like once we added Indexes and Indexes2 as file groups.
We return to the general Tab and added new files and positioning them to the corresponding Filegroup
- Logical name of DBLAB_Indexes attached to Indexes Filegroup
- Logical name of DBLAB_Indexes2 attached to Indexes2 Filegroup
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.
The execute permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'
We do not have permission to access xp_fixedrives. xp_fixedrives lists available storage on attached physical drives.
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
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.