Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for the ‘screen shot’ Category

A-Z of In-Memory OLTP : SSMS Enhancements for In-Memory

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.

CREATE DATABASE

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.

image

  • Open “New Database” interface, go to “Filegroups” tab. Then click on “Add Filegroup” under “Memory Optimized Data” section.

image

  • I have given name as “SQLSeverHelp_IMO_FG” and then went back to “General” tab.

image

  • Here is the little explanation of numbered steps.
    1. We need to come back to “General” Tab after creating filegroup in earlier step.
    2. Provide “Database name” SQLSeverHelp_IMO
    3. Click on “Add” button and provide details in new row (third row)
    4. We have given SQLSeverHelp_IMO_dir as logical name
    5. Since it is going to contain In-Memory optimized data, we need to use “FILESTREAM Data” in “File Type”
    6. Once we choose drop down in step 5, “Filegroup” would be “SQLSeverHelp_IMO_FG” created in previous screen.
    7. 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.

image

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.

image

  • 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.

image

  • Double click would open the template and we need to use “Ctrl+Shift+M” to get window called “Specify Values for Template Parameters”

image

  • 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.

CREATE TABLE

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)

image

Option # 2
If we have memory optimized filegroup in the database then we can choose “Create Memory Optimized Table”

image

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

image

All three options would open a query window with the template. (See, I told you earlier – no table designer)

image

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

image

In a nut-shell, in the absence of UI, you need to have T-SQL syntax knowledge.

CREATE PROCEDURE

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

image

Option # 2

image

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.

image

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:

Stay Tuned!

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOnPaperback, Kindle

Advertisements

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: , , , , , , , , | 4 Comments »

Step by Step – Configuring SQL Server 2012 AlwaysOn in Windows 7 Virtual Environment (Part 1)

Posted by blakhani on May 3, 2012


I have been demonstrating AlwaysOn feature to various audiences on various forums (TechEd, SQL Saturday, Virtual TechDays etc.). I have been using Windows Server 2008 R2 with Hyper-V to create multiple machine and demonstrate the feature. Someone asked me if he can do the same thing using his Windows 7 laptop? I tried looking at virtualization techniques available on Windows 7 and I found that it should be possible. I have tried to make it as descriptive as possible with step by step screenshots to configure whole setup.This topic would be a multipart series.

  1. Configure Windows 7 to use Virtualization and Configure three machines (One domain controller, two member servers). Also configure networking between them.
  2. Create Domain and configure the machines to make sure they are able to talk to each other.
  3. Install SQL Server 2012 on the servers.
  4. Configure AlwaysOn.

First download Windows Virtual PC on Windows 7 machine. Go to http://www.microsoft.com/windows/virtual-pc/download.aspx and choose below option. I am not going to Install Windows XP mode.

01_VPC

One clicked, choose your platform and language and click on download button.

02_VPC

This would install Windows Update to enable Virtual PC

03_Windows Installer

 

Restart the machine and you should be able to see below under "Start” > “Programs” > “Windows Virtual PC”

04_Windows Virual PC Shortcut

Next step would be to download the Virtual Hard Disk (a.k.a. VHD) file which I can use to configure my machine. Note that You need to use 32 bit images to make this work. Here is the link to download Windows Server 2008 images http://www.microsoft.com/en-us/download/details.aspx?id=14527 (Windows Server 2008 Enterprise Edition x86 (Full Install) VHD)

There are three parts which you need to download and then extract to user by Virtual PC. It would look like below.

05_Downloaded VHD

Double Click on the File and Extract the data into “C:\VHDs\DC” folder as shown below:

06_Extract

Once extraction is complete, make three copies of the vhd (Windows2008Fullx86Ent) file and keep them in separate folders as shown below. [I am showing only two, same exists for Srv2 also]

07_Copies

Lets add the machine to Virtual PC. Open the UI from "Start” > “Programs” > “Windows Virtual PC” > “Windows Virtual PC” and choose “Create virtual machine”

08_Create Machine 

Follow the wizard and create DC

09_Create Machine1 

10_Create Machine_Mem

11_Create Machine_Disk

One done, lets go to network setting and choose “Internal Network” This is because I don’t want to create domain controller in my office domain network.

Right Click on DC and choose “Settings”

12_Setting

Go to Networking option and choose “Internal Network”

13_Setting_Network

Now start the first machine and make sure we are able to log in.

14_DC_Startup

You need to follow few steps which are pretty self-explanatory. It would also as you to set machine name (I have given name as DC) new password for Administrator. Once you are done with that, this is what you should see 🙂

15_DC_LoggedIn

As homework, before moving to second part, you need to do the same thing with two more machines and here is what you should see in Windows Virtual PC.

16_Three Machines

See you at next part of Series!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in AlwaysOn, Denali, Images, Installation, Installation Guide, Introduction, screen shot, Screenshot, SQL Server 2012, Step by Step | 4 Comments »

Help : I lost sa password and no one has System Administrator (SysAdmin) permission. What should I do?

Posted by blakhani on February 8, 2012


If you are thinking that I am going to show you black magic to recover sa password or other login’s password then you have hit the wrong blog post. go back to search engine and search for better tool/utility. Smile

After spending many years with SQL Server product, I have seen situations where someone wants to get in to SQL Server as system administrator as someone recently left company who had System Administrator permission (or hundred other reasons). Till SQL 2000 days, it was impossible to solve such problem other than reinstalling SQL Server. Here are the typical questions I saw in forum:

  • Only sysadmin user is SA and I Lost SA password.
  • I am locked out of SQL server i.e. that no windows users are added (or removed them) as sysadmin and I forgot the password for sa
  • I am windows admin. How can I get sys admin privileges on SQL server express as I removed all sysadmin accounts from SQL.

Here are the various error you might see

  • Unable to create new database. This is generic error which means that you are not having permission.

SysAdminLost_1

TITLE: Microsoft SQL Server Management Studio
——————————
Create failed for Database ‘SQLServer-Help’.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • When you login to Management Studio, you would see only your own account and ‘sa’ as shown below

SysAdminLost_2

  • When you attempt to change password of ‘sa’ you might see below error.

SysAdminLost_3

TITLE: Microsoft SQL Server Management Studio
——————————
Change password failed for Login ‘sa’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Change+password+Login&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot alter the login ‘sa’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15151&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

All error messages appear because your account is not a System Administrator of SQL Server Instance.

So, what should you do now? Answer is simple, get yourself added as System Administrator. I know, you would say “don’t you think I have tried that as I got this error!”

SysAdminLost_4

TITLE: Microsoft SQL Server Management Studio
——————————
Add member failed for ServerRole ‘sysadmin’.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+ServerRole&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15247&LinkId=20476
——————————
BUTTONS:
OK
——————————

 

Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights.  To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)

Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]

  • Stop the SQL Server Service using ANY of below command.
    • Net Stop MSSQLServer (for default instance) / Net Step MSSQL$<InstanceName> If you want to know instance name, refer my earlier blog
    • Use SQL Server Configuration manager and stop the SQL service. [Start>Programs>Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager]
    • Use Services console [ Start > Run > Services.msc] and locate the SQL instance you want to stop.
  • Start SQL Server in Single User mode. You need to use start-up parameter m to start SQL Service in single user mode. I prefer command line but its your choice.
    • Using command line
      • net start MSSQLServer /m SQLCMD        [For default instance]
      • net start MSSQL$<InstanceName> /m SQLCMD     [For named instance]
    • Using configuration Manager
      • Locate the service which you have stopped earlier. Go to its properties, “Advanced”, click on drop down at “Startup Parameters”  and add ;-mSQLCMD as shown below

SysAdminLost_5

You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
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
——————————
BUTTONS:
OK
——————————

  • Connect to SQL Server and add desired account in SysAdmin role.
    I normally prefer to do it from SQLCMD (that’s why I added SQLCMD after m)
    • Open administrator command prompt. (i.e. right click on command prompt shortcut and choose “Run As Administrator”
    • Type sqlcmd –S <complete instance name> For example
      • sqlcmd –S. (for default instance)
      • sqlcmd –S.\MyInstance
        For getting exact name, your my earlier blog
    • You are connected as System Administrator, because you are part of local administrator group in windows.
    • At this point you can add any account to sysadmin because you are connect as sysadmin. Here is the script I normally use to add local administrator group as a part of SysAdmin group in SQL Server Instance. You may want to tweak this as per your needs because I am adding all local admin as sysadmin which is not a good practise.

USE [master]
GO
CREATE LOGIN
[BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master
..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

  • Here is how it would look on sqlcmd command prompt

SysAdminLost_6

  • Stop SQL Server Service. You can use any method as described in first step
  • Start SQL Service normally. This means that you need to remove startup parameter

Here is the MSDN reference for above, just to show that its documented.

So, to conclude, I have not shown any trick to recover any password. Just showed you detailed steps to gain sysadmin access provided you have windows admin rights.

DISCLAIMER: Use the method that is described in this article only as a failure recovery mechanism

Posted in Configuration Manager, ERRORLOG, forgot sa password, Forgot the SA password in Sql Server 2005, sa password lost, screen shot, Screenshot, Security, SQL Server, Step by Step | 38 Comments »