Installation of SQL Server 2014

In this post, I will demonstrate the installation of SQL Server 2014 on a VMWare Workstation. We can learn how to configure and use the features of SQL Server 2014.

1)      Make sure you run the setup as “ Run as Administrator “

2)      The SQL Server Installation Center window will display. Along the left side of the window are the categories showing how the SQL Server Installation Center is organized. On the right side of the window are different actions the installer can take.

3)      Clicking on Installation on the left side will display the different installation options. For this tip, we will click on "New SQL Server stand-alone installation or add features to an existing installation". This will launch the SQL Server 2014 Setup application.

4)      The SQL Server 2014 Setup application lists the steps it will follow on the left side of the window. I am installing SQL Server 2014 Enterprise edition here.

5)      Review the license terms, click on "I accept the license terms", and then click "Next”

6)      The SQL Server 2014 Setup application will run multiple checks for rules during the installation process. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed.

7)      The setup application will also search for product updates.

8)      Another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue.

9)      On the Setup Role screen, we will click on the SQL Server Feature Installation radio button. This will allow us to install the database engine, Analysis Services, Reporting Services, Integration Services and other features of SQL Server 2014. Click on "Next " to continue.

10)  The next step is the feature selection. This allows the installer to pick and choose the features to be installed. On the right side of the window, the disk space requirements are displayed. At the bottom, the installer can choose the path for the instance root and shared feature directories. Click on "Next" to continue.

11)  Another rule check after feature selection will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue

12)  The Instance Configuration screen allows the installer to specify the name of the instance and its ID. This screen will also display other installed instances.

13)  The Server Configuration screen shows the services to be installed, the service account name and the Startup Type.

14)  On the collation Tab, If you want to change the collation of the server select from the given option. Otherwise by default it will take the collation settings.

15)  On the Server Configuration tab of the Database Engine Configuration screen, select your preferred Authentication Mode and specify your SQL Server administrators.

16)  On the Data Directories tab of the Database Engine Configuration screen, specify your preferred directories for the data root, system database, user database, user database log, temp database, temp database log, and backup. Click on "Next".

17)  On the File stream Tab, you can select enable File stream option. I am not selecting the option as of now. We can choose this option later on.

18)  On the Server Configuration tab of the Analysis Services Configuration screen, select your preferred Server Mode and specify your Analysis Services administrators.

19)  On the Data Directories tab of the Analysis Services Configuration screen, specify your preferred directories. Click on "Next".

20)  On the Reporting Services Configuration, select Install and configure. Click on "Next" to continue.

21)  We have to specify Administrator for the Distributed Relay Controller Service.

22)  Specify the Name and directories for DRC Service.

23)  Another rule check will be run to ensure everything is in place so the setup will be successful. If a rule check fails, the setup application will provide the corrective measures to take so installation may proceed. Click on "Next" to continue.

24)  The Ready to Install screen displays all of the features and prerequisites to be installed. At this point, the installer can still go back to make changes or quit the process. Click on "Install" to begin the installation of the SQL Server 2014 components.

25)  The installation progress bar tracks the status of the installation.

26)  Upon completion of the installation, Check all the features are successfully installed and click on "Close" to exit the SQL Server 2014 Setup.

27)  After successful installation of SQL Server 2014, Check the Configuration Manager for features.

  

28)  Open SQL Server Management Studio.

29)  Expand the SQL Server and review all the components.

  

If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 

Have fun!

Warning 26003 : Uninstall "SQL 2008 R2 Express" (Common Files & Database Engine Shared) Error

Warning 26003 : Uninstall "SQL 2008 R2 Express" (Common Files & Database Engine Shared) Error

One day I was trying to install SQL Server 2008 R2 on UAT Server, so I could start with clean the previous installation. I uninstalled previous setup file from control panel.

After rebooting the server, I started fresh installation of SQL Server 2008 R2. In middle of the installation I got below error.

After investigation come to know that still some  files of previous installation are in server.

So while removing the files from control files got below error. I was not able to remove the installation files from control panel.

Below is the solution for this error.

Uninstall an existing SQL Server 2008 R2 instance, try the following steps:

1) Make sure you have administrative rights on the computer and backup all the user databases.


2) Uninstall the existing SQL Server and all the components from the control panel. Remember to backup the user databases before uninstalling SQL Server.

3) Backup (Export) the registry.

4)  Delete the following keys in regedit:
--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server--HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer

5)Goto HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall  and delete all the sub-keys referencing SQL Server.

6) Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and delete all the keys referencing SQL Server.

7)   Rename / Delete all the SQL Server folders in the computer.

8)   Reboot the machine.   

9)   Install SQL Server again.

Step by Step Always On Availability Groups in SQL Server 2012

This step-by-step has been created to help you get started in creating a SQL Server 2012 AlwaysOn Availability Group for your databases.

In order to follow this post, you need the following :

Three (3) VMware Host with following details:

1)      One Domain Controller (DC), Windows Server 2008 R2 Failover Cluster with 2 Nodes. (WIN1 & Win2). You can follow my post on setup Windows Server Failover cluster 

http://dbasqlserver13.blogspot.in/2013/08/microsoft-sql-server-2008-r2-failover_12.html

2)      Microsoft SQL Server 2012  (Standalone) Installed on Both the nodes. (WIN1 & WIN2)

3)      SQL Instance on WIN1 :          WIN1\WIN1SQL12

4)      SQL Instance on WIN2 :          WIN2\WIN2SQL12

5)      Assign Service accounts with domain permissions to SQL Service.

6)      Off the firewall Settings on both the node.

7)      Shared folder on the Primary Node.

8)      Make sure that you are able to connect both SQL Server Instances from both Node.

Enable Availability Groups on SQL Server

This needs to be done on all of the SQL Server instances that you will configure as replicas in your Availability Group. To enable the SQL Server 2012 AlwaysOn Availability Groups feature

1.      Open SQL Server Configuration Manager

2.      Select SQL Server Services

3.      Right-click on your SQL Server (in our example it is named WIN1SQL12) and select Properties

4.      Select the “AlwaysOn High Availability” tab and check "Enable AlwaysOn Availability Groups"

5.      Restart the SQL Server service.

Follow the same steps on Second Instance (WIN2SQL12)

Sample DB for Always On Configuration

Create a sample Database on WIN1SQL12 Instance (which we are going to set as a Primary Instance)

Here we are giving DB name as “Test_AlwaysOn”

  

Make sure that Recovery Model for the database should be Full.

Take Backup of Test_AlwaysOn DB in a shared folder, so we can copy the backup file on secondary Server and restore there.

 Open the shared folder on Secondary Node (WIN2) via Network Share.

Copy the backup file on Secondary (WIN2) Server.

Restore Test_AlwaysOn DB on Secondary Node (WIN2) in No recovery Mode.

Make sure you select RESTORE WITH RECOVERY Option.

So Now Database State will be in Restoring Mode.

Create and Configure SQL Server 2012 AlwaysOn Availability Groups

To create and configure a SQL Server 2012 AlwaysOn Availability Group,

1.      Choose any one instance to become the PRIMARY (say WIN1\WIN1SQL12)

2.      Open SQL Server Management Studio on WIN1\WIN1SQL12

3.      Expand the Management folder

4.      Right-click “Availability Groups” and select “New Availability Group Wizard…”

 In the Introduction page, click Next.

In the Specify Availability Group Name page, enter the name of the Availability Group in theAvailability group name: field. Click Next.

In the Select Databases page, select the checkbox beside the database that you want to include in your Availability Group.. Click Next.

In the Specify Replicas page, under the Replicas tab, click the Add Replicas button and connect to the other SQL Server instances that you joined as nodes in your Windows Server Failover Cluster.

 Configure the following options 

·         Automatic Failover (Up to 2) :          Checked

·         Synchronous Commit (Up to 3) :      Checked

·         Readable Secondary:                          Yes

In the Listener tab, select the Create an availability group listener option. Enter the following details.

 

·         Listener DNS name: Name that you will use in your application connection string

·         Port: 1433

Click the Add… button to provide an IP address. In the Add IP Address dialog box, enter your preferred virtual IP address in the IPv4 Address field. Click OK. Click Next.

In the Select Initial Data Synchronization page, select the Join option.

  

In the Validation page, verify that all validation checks return successful results. Click Next.

In the Summary page, verify all configuration settings and click Finish. This will create and configure the AlwaysOn Availability Group and join the databases.

In the Results page, verify that all tasks have been completed successfully.

  

View the Availability Group in SSMS

In SSMS, drill down to Management and Availability Groups. Here you will see your Availability Group. Drill down one farther and you'll see Availability Replicas, Databases, and Group Listeners.

To add a database, replica or view the dashboard right click on the Availability Group Name.

The dashboard will help you determine if your databases are Synchronized and Healthy

You can also check the Availability Group from Failover Cluster Manager.

  

Failover Testing

There are 2 ways via you can test your Always On Availability Failover.

    1)  Via Failover Cluster Manager.

    2)   Via SSMS.

      1)      Failover Always On Availability Group via Windows Failover Cluster Manger:

• Open Failover Cluster Manager
• Right Click on Always on Group. Currently Primary Node is WIN1.
• Move the Service or Application on Secondary Node
• Click on your Secondary Node (WIN2)

    

• Confirm Movement of Always on Group on Secondary Node. (WIN2)

  

• Confirm Successful Failover of Cluster on Secondary Node (WIN2)

  

• Confirm the Failover from SSMS.
• Check Availability Group on Primary and Secondary SQL Instance.
• Current Secondary Replica (Instance) : WIN1\WIN1SQL12

• The dashboard will help you determine if your databases are Synchronized and Healthy

• Current Primary Replica (Instance) : WIN2\WIN2SQL12

• The dashboard will help you determine if your databases are Synchronized and Healthy

     2) Failover from SSMS:

• Go to the primary Instance
• Right click on your Availability Group
• Click on Failover

•  Click on next on the Introduction Window.

  

• Select the primary Replica (Instance) on Which you want to failover your Always On Database.

   

• Provide the Credential and connect to the Primary Instance.

•   Click on Finish.

   

• Check the result for Successful Failover and click on Close.

• Check the Availability Group configuration after successful failover on both primary and secondary Replica (Instance)
• The dashboard will help you determine if your databases are Synchronized and Healthy
• Current Secondary Replica (Instance):           WIN2\WIN2SQL12

• The dashboard will help you determine if your databases are Synchronized and Healthy
• Current Primary Replica (Instance) :  WIN1\WIN1SQL12

If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. 

Have fun!