SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Restoring database without non-clustered indexes?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 04/22/2013 :  13:20:13  Show Profile  Reply with Quote
I'm moving to a new AWS instance that has 2 120 gig ephemeral SSDs. I don't trust ephemeral storage enough to put my main data files on it so I will be using NAS drives for them. Currently I am planning on putting just tempdb on it. But I was also considering having a separate data file for some largely read-only non-clustered indexes.

My question is, if the ephemeral drives were lost would I be able to restore the database without some of the non-clustered indexes (which I could recreate from scripts after the restore).

I am planning on testing this scenario but am looking for some expert advice.

Thanks.

Edited by - ferrethouse on 04/22/2013 13:21:01

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 04/22/2013 :  18:19:57  Show Profile  Reply with Quote
My testing indicates that sql server does not enjoy having drives with indexes on them disappear. I think I'll play it safe here and not put any indexes on ephemeral drives.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/23/2013 :  11:07:46  Show Profile  Visit russell's Homepage  Reply with Quote
NAS is a poor choice for database files.
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 04/25/2013 :  18:13:39  Show Profile  Reply with Quote
quote:
Originally posted by russell

NAS is a poor choice for database files.



It is the only real choice in a virtualized cloud environment like AWS. I've seriously considered the local SSDs but don't like the risk since they are ephemeral. We've been running in AWS for about a year now and with provisioned IOPS we get pretty good performance from their EBS volumes (NAS). Can provision up to 2000 IOPS guaranteed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000