I upgraded one of my Lab Databases from v2008-R2 to v2012 a day or so ago.
As I started a Performance Test run against the database I noticed that I could no longer connect to the In-Memory database.
The SQL Instance itself was online, but just the lone database that has the In-Memory table.
Reviewed MS SQL Server Error Log
Review MS SQL Server Error Log and noticed quite a few helpful relevant error lines.
Using Local Policy, Grant SE_MANAGE_VOLUME_NAME
What is Service Account?
Launched Services Applet and determine which Account SQL Server is “running as”:
… and it is “NT Service\MSSQLSERVER”.
Review Local Security Policies
We are interested in the following Policies:
- Lock pages in memory
- Perform volume maintenance tasks
Grant permissions to Service Account
Looked on the Net for how to grant Local Policies via Command Line, and found good from who else Kendra Little:
I do not even have to vent, as I know Kendra is smart and she shares the truth.
ntrights -u "NT Service\MSSQLSERVER" +r SeLockMemoryPrivilege ntrights -u "NT Service\MSSQLSERVER" +r SeManageVolumePrivilege
ntrights -u "NT Service\MSSQLSERVER" +r SeLockMemoryPrivilege Granting SeLockMemoryPrivilege to NT Service\MSSQLSERVER ... successful ntrights -u "NT Service\MSSQLSERVER" +r SeManageVolumePrivilege Granting SeManageVolumePrivilege to NT Service\MSSQLSERVER ... successful
… restart MS SQL Server and dependent services.
net stop mssqlserver /y net start mssqlserver
Changed Microsoft SQL Server – Max Server Memory
As I am not yet quite yet ready to dig into resource pool allocation and management, I took the easy route per reviewing if I have in place a “max server memory”. And, I do at a measly 400 MB.
As we are now using in-memory, I need at minimum enough memory to cover the in-memory infrastructure and database objects.
Let us start @ 1 GB.
exec sp_configure 'max server memory (MB)', 1000 go reconfigure go
Brought Database Online
ALTER DATABASE [DBLabInMemory] SET ONLINE; go