Search This Blog & Web

Thursday, June 22, 2017

Creating Alias name for MSSQL Server Instance

Recently we shifted our production database server to new SQL Server instance. Once that activity completed we face a lot of issues to change connection string for running application from one server to other. One the major application is SharePoint because you can’t open Central administration from SharePoint server unless your instance name will be same as before.
After doing some research we find a way around for this problem. We can create database server Alias and Alias name will be same as previous database instance name so that all applications will work as they are working before. Other possible uses to create aliases are database server security in order to hide you database server name or you can create it when Named instance is there to make it easy.
In following scenario, we have Database server with Named Instance. We created Alias to qualify simple name and smooth application execution without changing much. Following are the steps to create Alias.

Open SQL Server configuration manager and go to SQL Native Client Configuration. Click to Aliases as shown in diagram


You need to provide following information
Alias name: Name that you want to set for Database server
Port number: Write dynamic or specific port to connect
Protocol: It can be TCP/IP preferably or Name pipe. Selected protocol must be enabled in client protocols.
Server Name: For named instance you need to mention machine\instance name, For default instance we can mention local host.

You can mention specific port number or you can find dynamic port from SQL Server Network Configuration. Open TCP/IP properties and go all the way down you will see IPAll.

If you have 64 bit operating system you need to configure Aliases in SQL Native Client 11.0 configuration and SQL Native Client 11.0 Configuration (32bit).


In the following screen shot. You can see both Instances are connected.


Sunday, May 28, 2017

Changing and verifying SQL Server Status from Linux (Ubuntu)

Recently we learn how to install SQL Server on Linux and how to use it. As a database administrator we need to check current database status as well as need to enable and disable it on demand. In the following post we will learn how to check current database status and change it using Linux command shell.

Once you install MSSQL Server in Ubuntu, You need to restart Linux machine in order to finish installation. SQL Server engine will automatically start as “Active” after restart. In the following post we will learn how to verify and change its status like Start, Stop, Disable and Enable.
1-      Verify running status for MSSQL Server using following command
“ systemctl status mssql-server”
You can see the status in green color.

2-      We can change MSSQL Server status using following sudo command
“sudo systemctl stop mssql-server”
To verify current database status you will repeat same step as above “ systemctl status mssql-server”.

3-      Using following command We can disable MSSQL Server engine using following sudo command
“sudo systemctl disable mssql-server” and can verify by executing as “ systemctl status mssql-server”.

4-      To enable MSSQL Server engine we can using same sudo command
“sudo systemctl enable mssql-server” and can verify by executing as “ systemctl status mssql-server”

5-      Finally MSSQL Server is up and active.

Thursday, May 25, 2017

SQL Server on Linux step 3: Linux (Ubuntu ) Installing SQL SERVER vNext

In my previous post we learn how to install Linux through Oracle Vitual box that you can found from following link

After finishing your Ubuntu installation, you are ready to install SQL Server. You can install it through Ubuntu or your host windows using command line. Installing from Windows you need to install putty easily available on internet. Install it and connect it using Network IP and run all steps mentioned below. 
I am installing it through Ubuntu plate form and following are the steps for that

1-      Execute “sudo apt-get update”
2-      Execute “sudo apt-get upgrade” is an optional step

3-      Run curl command to import GPG key. GPG keys also provide cryptographic privacy and authentication. If curl command is not installed on this machine so we need to run the suggested command to install the curl command first.

4-      After successful installation of curl, we can import key using following command and add it using sudo command.
“curl | sudo apt-key add –“

5-      Due to some network and internet problem I was unable to register key directly from Microsoft site. So if key download fails download key from mentioned path. you can find it in downloads, Open the file, Save it as .asc file and open it in terminal

6-      From the terminal windows register key from the following command
“ sudo apt-key add microsoft.asc”

7-      After successfully registering GPG key download mssql server from following path.
“curl | sudo tee /etc/apt/sources.list.d/mssql-server.list”

8-      You can check the folder and rights to the downloaded file by executing ls -lrt
“ls –lrt /etc/apt/sources.list.d”

9-      After executing all run “sudo apt-get update” before installing SQL Server.

10-   Now you are ready to install SQL Server now. Start SQL Server installation using following command.
“sudo apt-get install –y mssql-server”

11-   Installation will continue as we can see installation percentage. It will take some time depending on internet connection. Installation will complete with the config setup message.

12-   As mentioned in the message, To complete SQL Server setup run following command
“sudo opt/mssql/bin/mssql-conf setup”
It will prompt license agreement message.

13-   After completing above step Microsoft will ask for administrator password and will start SQL Server. During my installation I set password as “vSqlserver2017”. In the end you will get Setup completed successfully message.

14-   You have successfully completed MSSQL Server installation. On restarting you linux server, you can check SQL Server status by executing following command.

15-   We need to Install SQL Server tools by using following command. Using wget we need to download and copy prod.list on sources.list.d path.

16-   On getting permission message I am unable to copy on given path. I used same path as I did to register key for SQL Server.
17-   I downloaded prod.list file and copy it to sources.list.d

18-   Opening root as file manager by entering following command
“sudo nautilus”

19-   Following root path will open

20-   Reaching at sources.list.d path copyà paste prod.list as follows

21-   It is recommended to run get-update command before and after any installation and then start installing tools by following command
“sudo apt-get install mssql-tools –y”

22-   Installation will start once all required data will be downloaded.

After completing installation we are going to perform basic SQL Server queries using linux Ubuntu command prompt.

23-   SQL Server is active and running. You can connect SQL Server using following command “sqlcmd –S (Server name) –U (username) –P (Password)”

24-   Performing basic SQL Server testing through command prompt.
a.       Create  database
b.      Create Table
c.       Insert data
d.      Query data