SQL Server 2014 has an awesome feature of having database and tables In-Memory and do online transaction processing. For doing In-Memory OLTP, the database and table should support Memory OLTP. This can be achieved by creating a new database in SQL Server 2014.
Situation may arise, that you need to upgrade the SQL Server 2005 or 2008 to 2014. In this case the database will be already available. Now how to make the existing database compatible for In-Memory OLTP? Making an existing database to support in-Memory OLTP is easy. Follow the below steps to do that.
Steps to Make a Database Compatible for In-Memory OLTP
- Open SSMS.
- In the object explorer, select the database you want to support In-Memory OLTP.
- Right-click and select properties.
- In the properties window, go to the Filegroups page. You can see the MEMORY OPTIMIZED DATA section.
- Click the Add Filegroup button and enter the name of the filegroup.
- Now, go to the Files page.
- Click the Add button and enter the Logical Name, Select the File Type as FILESTREAM Data and change the file path if needed.
- Click OK.
The database is now optimized for In-Memory OLTP.
If you want to create a fresh database in SQL Server 2014, then follow the steps in the article “Creating In-Memory OLTP Compatible Database“.