Posted by blakhani on December 10, 2013
Microsoft believes that everyone in the world is not a developer there are some DBAs as well. Be it Visual Studio, SQL Server or any other programming paradigm – Microsoft makes it easy for developers and DBA alike. In my previous post, you would have read about new T-SQL constructs added to support In-Memory OLTP objects. This blog post shows the SSMS improvements and dialogs introduced with SQL Server 2014 to work with In-Memory OLTP.
We have learned earlier that we need a new filegroup which would contain memory_optimized_data. This is the key point to understand the flow of actions below..
- Right Click on database folder in SSMS and Choose “New database”. This is the same place where we create regular database.
- Open “New Database” interface, go to “Filegroups” tab. Then click on “Add Filegroup” under “Memory Optimized Data” section.
- I have given name as “SQLSeverHelp_IMO_FG” and then went back to “General” tab.
- Here is the little explanation of numbered steps.
- We need to come back to “General” Tab after creating filegroup in earlier step.
- Provide “Database name” SQLSeverHelp_IMO
- Click on “Add” button and provide details in new row (third row)
- We have given SQLSeverHelp_IMO_dir as logical name
- Since it is going to contain In-Memory optimized data, we need to use “FILESTREAM Data” in “File Type”
- Once we choose drop down in step 5, “Filegroup” would be “SQLSeverHelp_IMO_FG” created in previous screen.
- Provide complete physical “Path” where the files/data needs to be stored for In-Memory tables. C:\IMO_Database\SQLSeverHelp_IMO_dir
Follow sequence given in above image and hit OK.
Do you know that I can remember all command by heart? I am joking! If you want to script this action, use the “Script” button (shown in below image). This would give us the same script (with little more setting) which we use in previous part.
The “Script” feature is available in almost every user interface of SSMS. Another option is to use predefined template. This is one of under-utilized/less known feature of SSMS. As shown below, go to “View” Menu and click on “Template Explorer” (Ctrl+Alt+T) to access a lot of predefined scripts.
- Once we click on “template explorer”, we would see “template browser” opened within management studio. Over there, we can see many template and one of them of our interest, as of now, is highlighted in below image.
- Double click would open the template and we need to use “Ctrl+Shift+M” to get window called “Specify Values for Template Parameters”
- We can make changes here and hit OK. We can edit path, name and modify the TSQL as per our requirement.
If you have an existing database then, ALTER DATABASE would be needed. This is because the filegroup for memory_optimized_data might not have been defined. We can go to database properties and add new filegroup and follow same screen with 7 steps.
Next step is to create an In-Memory Optimized table. Note that there is no “table designer” UI to create the In-Memory Optimized table. If you have followed earlier steps, template explorer might be open (else press Ctrl+Alt+T)
Option # 1
In situations where we have an existing database which is not having memory_optimize_data filrgroup defined, we can use template called “Add MEMORY_OPTIMIZED_DATA Filegroup and Create Memory Optimized Table” (shown below)
Option # 2
If we have memory optimized filegroup in the database then we can choose “Create Memory Optimized Table”
Option # 3
If we go with conventional way of creating table using SSMS then we can Right Click on “Tables” folder under the database SQLSeverHelp_IMO and choose New > Memory Optimized Table
All three options would open a query window with the template. (See, I told you earlier – no table designer)
In this query windows, we need to use Ctrl+Shift+M and replace the parameter as needed. We can add more columns, indexes and make modifications. As the commented section in above template says, we need to have filegroup defined for memory_optimized_data
In a nut-shell, in the absence of UI, you need to have T-SQL syntax knowledge.
Same as CREATE TABLE, there is no special interface available for natively compiled procedure. We can use either Right Click technique (#1) or template browser (#2) to reach to template.
Option # 1
Option # 2
Both of the options above are going to open template and then we can write the code there. Remember the shortcut Ctrl+Shift+M for filling template parameter.
Till now we have learned how to create objects. In next post, we would see what happens differently (than regular objects) when in-memory tables are in-memory stored procedures are created. Stay tuned!
Missed reading earlier posts? Here is the quick reference:
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
Posted in A - Z Series, Hekaton Series, In Memory OLTP, In-Memory OLTP, Introduction, screen shot, Screenshot, SQL Server 2014, SQL Server Management Studio, SSMS | Tagged: A-Z-Series, Hekaton, Hekaton Series, HekatonSeries, In-Memory OLTP, inmemory oltp, Series, SQL Server 2014, ssms | 4 Comments »
Posted by blakhani on December 6, 2013
First of all this is not normal to start SQL Server in single user mode. So if you are reading this blog by getting this as a search result, you might be in a tough situation. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode.
Okay, so you have started SQL Server in single user mode by specifying start-up parameter “m” either by net start MSSQLServer /m or via command prompt sqlservr.exe –m –sInstanceName but when you are trying to connect via any tool (SQLCMD, OSQL, SQL Server Management Studio or any other) you are welcomed by error message.
TITLE: Microsoft SQL Server Management Studio
Error connecting to ‘(local)\SQL2k8R2’.
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
Error message is clear that someone “else” has made connection to SQL Server and due to /m switch, SQL is not letting you get in. Now, logically there are two ways to fix this problem.
- Find out who is connecting before you and stop that application (difficult in real/disaster time)
- Ask SQL Server to not to allow anyone except me.
Second one sounds more easy. So let’s discuss that.
If you want to know the root cause of “why its in single user mode” then go back and look for SQL Error Log and you may find something as below.
In above Errorlog, we can see additional start-up parameter and warning that SQL is in single user mode.
Essentially we want to start in single use mode and no one else except you should be able to connect. Books online has explained this clearly that you can append m parameter with the client application name. Here are few example
- Only SQLCMD should be able to connect then it would be m”SQLCMD”
- If you want to use Management studio only then it would be m"Microsoft SQL Server Management Studio – Query".
Let’s see it in action. I can add start-up parameter in multiple ways. My favorite is staring SQL from command line as a service, My instance name is SQL2K8R2 so below would be the command
Net Start MSSQL$SQL2K8R2 /m”SQLCMD”
If you have default instance than it would be
Net Start MSSQLServer /m”SQLCMD”
Once you have started SQL Service in Single use mode then only SQLCMD application can connect and other connection would get error message.
Let’s see what we nave in ERRORLOG
2013-12-06 09:13:50.08 Server Registry startup parameters:
-d E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
-e E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2013-12-06 09:13:50.08 Server Command Line Startup Parameters:
2013-12-06 09:13:50.80 spid4s Warning ******************
2013-12-06 09:13:50.80 spid4s SQL Server started in single-user mode. This an informational message only. No user action is required.
2013-12-06 09:14:32.93 Logon Error: 18461, Severity: 14, State: 1.
2013-12-06 09:14:32.93 Logon Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
It’s important to note that string after –m parameter is case-sensitive. This means that if you give sqlcmd (all lower case) then connection can’t be made. If we want to use management studio then the parameter would be –m”Microsoft SQL Server Management Studio – Query”. This is the exactly same name as in program_name when you look at sys.processes or sys.dm_exec_sessions
Hopefully this blog would help you in making a connection to SQL Server without stopping application, changing password, disabling account as there were the tricks I have seen to get into SQL when only one connection can be made and unfortunately that’s not you.
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
Posted in Connectivity, Error, Screenshot, Step by Step | Tagged: disaster, only one administrator can connect at this time, restore master, Server is in single user mode, sql server, sqlcmd | 17 Comments »