I have a test suite that runs against a database in a SQL Server 2012 cluster. I want this test suite to run as fast as possible. I'm willing to sacrifice every durability and availability guarantee for performance. The database is recreated during every test run, so it doesn't even have to survive server restarts.
Changing the recovery model with ALTER DATABASE [dbname] SET RECOVERY SIMPLE makes no noticeable difference.
A good option seems DELAYED_DURABILITY, but this is new in 2014 and therefore unavailable to me.
What can I do to make a crazy fast database on this cluster? I tried looking for in-memory databases but couldn't find any options. The cluster won't allow me to create a database on a local disk, insisting that it must be located on a clustered disk.
Update: The application uses advanced SQL Server features, so I'm most likely stuck with MS SQL Server. The database itself is quite small because it's for testing (8MB mdf, 1MB ldf). The cluster nodes are the fastest servers in the network, so if I can misuse one of these nodes for an in-memory database that would certainly be fastest. But how?
If for some reason you are stuck on a clustered sql server instance but you do not want durability, maybe you could run your application on tempdb. Tempdb can be placed on local storage to avoid cluster overhead.
Also note that data stored on tempdb will initially stay on the buffer pool, which is RAM memory, and only spill to disk asynchronously as the sql server engine finds a better use for that memory space.
You can implement this solution, by scripting all your database objects and using a text editor to replace the name of your database with 'tempdb'. Then execute this script to create all objects on tempdb. Also set the initial catalog on the user running the application to tempdb and/or edit the required connection strings. Keep in mind that tempdb is regenerated every time the instance is restarted. So you would loose all data and ddl changes.
That would certanly be a best effort to "sacrifice every durability and availability guarantee".