Archive for the ‘SQL Server’ category

How to View or Restrict the Number of Connections in SQL Server

December 30th, 2016 by Admin

SQL Server allows a maximum of 32,767 user connections. In this tutorial we’ll show you how to find out the number of connections to a database in SQL Server, and restrict the number of connections to a SQL Server instance.

Part 1: View Total Number of Connections in SQL Server

Need to check the number of active connections for each database? This can be done easily using the following script. The script displays the databaseName, the number of connections and the login name:

SELECT DB_NAME(dbid) as DBName,
loginame,
COUNT(dbid) as NumberOfConnections
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

If you’re running SQL Server 2012, 2014 or 2016, run this script instead:

SELECT DB_NAME(database_id) as DBName,
login_name,
COUNT(session_id) as NumberOfConnections
FROM sys.dm_exec_sessions
GROUP BY database_id, login_name

Part 2: Restrict Concurrent Database Connections in SQL Server

To configure the maximum user connections, open SQL Server Management Studio and connect to your database, then follow these steps:

  1. In Object Explorer, right-click a server and click Properties.

    sql-server-properties

  2. Click the Connections node on the left page.
  3. Under Connections, in the Maximum number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server.

    max-connections-in-sql-server

  4. Click OK. Restart SQL Server to apply your changes.

5 Ways to Stop or Start SQL Server Service

October 11th, 2016 by Admin

How to stop a local SQL Server instance from command line or PowerShell? In this tutorial we’ll show you 5 simple ways to stop or start SQL Server service in SQL Server 2016/2014/2012/2005.

Method 1: Stop / Start SQL Server Service from Microsoft Services Console

  1. Press the Windows + R keys to open the Run box. Type services.msc and press Enter.
  2. This will open the Microsoft Services console. Find the service called SQL Server (instant name) and then double-click on it.

    sql-server-service

  3. In the Properties dialog box, you can see that the service name of my SQL Server instance is MSSQL$SQLEXPRESS. From there you can click the Stop button if your SQL Server service is running, or click the Start button to get the service up and running.

    stop-sql-server-service

Method 2: Stop / Start SQL Server Service from Command Line

  1. Open an elevated Command Prompt.
  2. To stop a named SQL Server instance, type the following command. Replace SQLEXPRESS with the instance name.
    net stop MSSQL$SQLEXPRESS

    start-sql-server-command-line

    To start a named SQL Server instance, type:
    net start MSSQL$SQLEXPRESS

Method 3: Stop / Start SQL Server Service from SQL Server Configuration Manager

  1. To get started, you need to open SQL Server Configuration Manager. In Windows Server 2008 or 2003, you can click Start > Programs -> Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.
  2. In the left pane of SQL Server Configuration Manager, click SQL Server Services. We can see all services in right pane. Right-click on SQL Server service and you can then select Stop or Start from the context menu.

    sql-server-configuration-manager

Method 4: Stop / Start SQL Server Service from PowerShell

  1. Open the PowerShell as administrator.
  2. You can use the Start-Service cmdlet to stop your SQL Server service. Replace SQLEXPRESS with the instance name.
    Start-Service -Name 'MSSQL$SQLEXPRESS'

    start-sql-server-powershell

    Or use the Stop-Service cmdlet to start your SQL Server service.
    Stop-Service -Name 'MSSQL$SQLEXPRESS'

Method 5: Stop / Start SQL Server Service from SQL server Management Studio

  1. Open SQL server Management Studio and connect to your SQL Server instance.
  2. Right-click on the server in Object Explorer, you’ll have options to Stop, Start, Pause, Resume and Restart your SQL Server service. If we are not already connected, we can’t start a stopped instance.

    stop-sql-server-via-ssms

How to Install SQL Server 2016 CTP 3.0 on Windows Server 2012 R2

November 26th, 2015 by Admin

SQL Server 2016 Community Technology Preview 3.0 (CTP 3.0) was released to the public on 28th October, and I downloaded a fresh copy to start trying it out. In this article I’m going to explain, step-by-step, how to install SQL Server 2016 CTP 3.0 instance on a Windows Server 2012 R2 system.

Preparing for installation

Before beginning, you should check the minimum hardware and software requirements before you install SQL Server 2016. both .NET framework 3.5 and Windows 8.1 Update (KB2919355) are required to be installed. Check out the following articles on how to install .NET framework 3.5 and KB2919355 on Windows Server 2012 R2:

Install .NET Framework 3.5 on Windows Server 2012 with / without CD
Fix: Windows 8.1 Update (KB2919355) is not applicable to your computer

How to Install SQL Server 2016

SQL Server 2016 CTP 3.0 is free for trial and you need to sign-in to download it from Microsoft’s web site. The ISO file I’ve downloaded is SQLServer2016CTP3.0-x64-ENU.iso that takes around 2.58 GB of space on disk. Once downloaded, you can burn the ISO image to a DVD for making a installation media. Here’s how to install SQL Server 2016 on Windows Server 2012 R2:

  1. Insert the SQL Server 2016 installation DVD. From the root folder, double-click Setup.exe and you’ll be greeted with SQL Server Installation Center. To perform a new stand-alone installation, click New SQL Server stand-alone installation or add features to an existing installation.

    sql-server-installation

  2. Enter the product key and click Next. As the final version of SQL Server 2016 is not released yet, so just select evaluation edition.

    sql-product-key

  3. In the License Terms window, tick the box I accept the license terms and then click Next to continue.

    accept-license-terms

  4. A new option appears here to use Microsoft updates if you would like to. I keep it unchecked as I would patch SQL Server manually at the right time, after testing.

    use-microsoft-update

  5. Run the setup rules checks. I have a warning to tell me that the firewall is on and just ignore it if you haven’t configured Windows firewall to block SQL Server access.

    sql-install-rules

  6. On the Setup Role page, choose SQL Server Feature Installation and click Next.

    sql-server-feature-install

  7. Select the required SQL features and click Next. Make sure that Database Engine Services is checked. I have also chosen to install the Management Tools – Basic so I can use Management Studio to manage SQL Server.

    feature-selections

  8. At this step another check will run, this time to identify problems that might block the setup, based on your choices of the last step. Just wait for its completion and click Next.

    sql-server-feature-rules

  9. This step lets you decide if you want to go with Default Instance or a Named Instance. Be aware that you can only install one default instance but you can install multiple named instances on the same computer.

    configure-sql-server-instance

  10. On the Server Configuration screen, you can specify the service accounts and startup type, or simply leave it at the default configuration as you can change them after the installation.

    sql-service-accounts

  11. Now you need to choose the authentication mode of your SQL Server instance. For the purposes of this article, I will be choosing Mixed Mode Authentication. You will also need to specify the SQL Server administrators to be used; in this example I will use the current logged in user by clicking Add Current User button.

    sql-server-password

  12. This page shows a tree view of installation options that were specified during Setup. Review and click Install.

    ready-to-install-sql-server

  13. The installation will begin — wait for completion.

    sql-installation-progress

  14. Once the installation is complete, SQL Server 2016 Setup will confirm this showing which items of the install succeeded.

    sql-install-succeeded

2 Options to Reset Forgotten SQL Server 2012 Password

June 10th, 2015 by Admin

Forgot the SA password for your SQL Server 2012 instance? If you have tried to access the database too many times with wrong passwords, the SA account may be locked out. In this tutorial we’ll show you two options to take control of your SQL Server 2012 by either adding a sysadmin account or resetting the SA password.

Option 1: Reset SQL Server 2012 Password in Single-user Mode

  1. Launch the SQL Server Configuration Manager utility. Look for your SQL Server 2012 instance and stop the service.

    stop-sql-server

  2. Open a Command Prompt with admin privileges, run the following command to start SQL Server 2012 in single-user mode:
    net start MSSQL$SQLEXPRESS /m"SQLCMD"

    Be sure to change MSSQL$SQLEXPRESS to the service name of your SQL Server instance. Each instance has its own SQL Server service. The service name could be found within the Windows Services panel (services.msc).

    sql-server-service

  3. Use the sqlcmd command to connect to your SQL Server 2012 database locally. If your SQL Server is installed on a remote computer, make sure you substitute the . (dot) with the host name of that server.
    sqlcmd -E -S .
  4. Type the following SQL statement to create a new account and granting it any server-level permission.
    CREATE LOGIN tom WITH PASSWORD = 'P@ssw0rd'
    GO
    ALTER SERVER ROLE sysadmin ADD MEMBER tom
    GO

    add-sql-user

  5. Type exit to quit sqlcmd. Restart your SQL Server 2012 service to get out of the single-user mode. You can then connect to the database using the new login you just created.

Option 2: Offline Reset SQL Server 2012 Password

SQL Server creates the user’s password hash, which is stored in the master database (sysxlogins). With SQL Server Password Changer you can unlock / reset SQL Server 2012 passwords for the SA and other user accounts. Here’s how:

  1. First of all, you need to stop your SQL Server 2012 instance from within the SQL Server Configuration Manager utility.
  2. Download and install the SQL Server Password Changer program.
  3. Start the SQL Server Password Changer program. Click on Open File button to browse for the master.mdf file. Usually the master.mdf file is located at C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA.

    open-master-mdf

  4. Once you have clicked on the Open button, the program will display a list of user accounts for your SQL Server instance. Select the SA account and click on Change Password button.

    sql-server-2012-password

  5. In the pop-up window, type a new password and click OK. The new password can be as short or simple as you want, no need to meet the password complexity requirements.
  6. After resetting the SA password, start your SQL Server instance and you can then login to the SA account successfully.

2 Methods to Start SQL Server in Single User Mode

October 26th, 2014 by Admin

What is single-user mode?

There are certain situations when user may have to start a SQL Server instance in single-user mode. For example, you might need to restore your master database from a backup in the event of a failure or damage, detach the database and kill all the connections. Both actions require starting an instance of SQL Server in single-user maintenance mode.

When a database is started in single-user mode, the following events occur:

  • Any current connections to the database are dropped without warning
  • Only one user can connect to the database
  • The CHECKPOINT process is not started

I’m going to walk you through the process of putting your SQL Server database into single-user mode. This can be either done via SQL Server Configuration Manager by setting the startup parameters or through the Command Prompt.

Method 1: Start SQL Server in single-user mode through SQL Server Configuration Manager

Run SQL Server Configuration Manager. Choose SQL Server Services from the left panel and then right-click on desired SQL Server service that needs to run in single-user mode. Select Properties from the drop-down menu.

configuration-manager

In SQL Server 2014 or 2012, click Startup Parameters tab. Type -m in the Specify a startup parameter box and then click Add.

sql-startup-parameters

If you’ve installed SQL Server 2008, 2005 or an earlier version, click Advanced tab. Append the ;-m to the end of the Startup Parameters box.

startup-parameters

Click Apply to save your changes. Restart your SQL Server instance and it should then run in single-user mode.

Method 2: Start SQL Server in single-user mode through Command Prompt

First of all, you need to figure out the name of your SQL Server instance. Press Windows key + R to bring up the Run box. Type services.msc and press Enter to open the Services window.

Find your desired SQL Server service in the list, double click it. This will open the Properties dialog box for that service. Copy the service name listed there. In my example, the service name is MSSQL$SQLEXPRESS.

sqlserver-service

Open up a Command Prompt with administrative privileges and run the following command to stop the SQL Server service.
net stop service_name

The next step is to start the service in single-user mode. This is done by specifying /m parameter with net start command.

single-user-mode

How to know if a running SQL Server instance is in single-user mode

When your SQL Server instance is started in single-user mode, it prevents multiple clients from connecting to the server at the same time. For example, if you’ve already connected to your DB with the SA account, and then try to login again through SQL Server Management Studio (SSMS), you’ll get the following error message:

Login failed for user”. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

sqlserver-connect-error

To fix this issue, you need to remove -m from the startup parameters and restart your SQL Server instance, which will bring the database back to multi-user mode.

How to Enable Remote Connections in SQL Server 2014/2012/2008

October 23rd, 2014 by Admin

When you try to connect to a SQL Server instance from a remote computer, you might receive the following error message:

Cannot connect to SQL Server.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)

After installing SQL Server, the remote connection feature is disabled by default, and you’re only allowed to connect to your database locally. This article explains how to configure remote connections for a default SQL Server 2014/2012/2008 instance that you want to connect to from a remote computer.

How to Enable Remote Connections in SQL Server 2014/2012/2008?

Open the SQL Server Management Studio and log on to your DB locally. Right-click server name in the left pane and select Properties.

Select the Connections page in the left pane and make sure that checkbox Allow remote connections to this server is selected. Once this is done, exit SQL Server Management Studio.

allow-remote-connection

Open the SQL Server Configuration Manager, expand the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is). Make sure that TCP/IP protocol is enabled.

enable-tcpip

Right-click on TCP/IP and select Properties. In the TCP/IP Properties dialog, select the IP Addresses tab and scroll down to IPAII. Make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433.

tcpip-properties

Click OK to save your changes and restart your SQL Server instance. Now you should be able to connect to your SQL Server instance remotely using SQL Server Management Studio.

connect-to-sqlserver

If there is still problem in remote connection, you most likely need to configure your firewall settings and make sure the TCP port 1433 is not blocked by your firewall.

How to Reset SQL Server 2008 Password without Reinstalling

June 7th, 2014 by Admin

What to do when the SA account password is lost in SQL Server 2008? You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the built-in\Administrators from the sysadmin server role and now you’ve lost admin access to your SQL Server instance. At this point you may think that the only option is to reinstall SQL Server and attache the database.

In this tutorial I’ll show you how to reset SQL Server 2008 (R2) password without reinstalling. SQL Server Password Changer is a great recovery utility that allows you to change SA password that is stored in the master database file (master.mdf). Here’s how.

How to Reset SQL Server 2008 Password without Reinstalling?

  1. Click here to download and install the SQL Server Password Changer program on your computer.
  2. Click the Open File button. Select the SQL Server 2008 master database file (master.mdf). Typically it is located in the folder: c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\.
  3. The program will automatically decrypt the master database file and display all user accounts in your SQL Server. Select the SA account, click the Change Password button. Type a new password and click OK.

    SQL Server Password Recovery

  4. The program will replace the existing password with your new password, and also unlock your SA account if it’s already locked out or disabled. Now start your SQL Server 2008 instance again and you can then log into the SA account with the new password.