Advertisements

Archive

Archive for the ‘Linux’ Category

Connect SQL Server on Linux with SSMS from a Windows machine

January 6, 2017 1 comment

 
In [previous post] we saw how to install & setup SQL Server on Linux. We used PuTTY to connect to the Linux Azure VM and query SQL Server there.

Here in this post we will try to connect to SQL Server on Linux via SSMS from a Windows machine.
 

So to connect remotely to SQL Server on an Azure VM, you have to configure an inbound rule on the network security group. The rule allows traffic on the port on which SQL Server listens (default is 1433).
 

–> Setup Inbound Rule:

1. You need to go to portal.azure.com and login with your credentials. On the Dashboard you will see your Linux VM as shown below:

linux-ubuntu-on-azure-07
 

2. Click on the Linux VM widget, it will open up and list out the Settings, click on Network Interfaces, and select the available Network Interface from the list.

install-sql-on-linux-09
 

3. Now click on the Network Security Group:

install-sql-on-linux-11
 

4. On the Network Security Group page, select the Inbound security rules, and click on the “+” button create new for SQL Server:

install-sql-on-linux-12
 

5. On the Add inbound security rule page, provide a name to this rule, select “MS SQL” under Service drop-down, and click OK:

install-sql-on-linux-13
 

–> Connect using SSMS:

6. Now from any Remote machine or even you PC which has SSMS installed and have internet connection, try connecting the SQL Server Linux Instance:

– Server Name: provide the IP address of the Linux VM (you can get it by opening the “Linux VM widget” and clicking on “Connect” button at top)

– Authentication: should be SQL Server Authentication

– Login: “SA”

– Password: apply the same password that you set it up while Configuring SQL Server on Linux.

install-sql-on-linux-14
 

7. On successful authentication you will see the Object Explorer showing the SQL instance that’s running on a Linux machine. On a glance you can’t make out any difference b/w a Linux SQL instance or a Windows SQL instance.

Thus I executed the SELECT @@version statement which shows the SQL Server version and on which Operating system its running.

You can even see the Database SQLdbOnLinux that we created by connecting from the PuTTY client.

install-sql-on-linux-15
 

That’s it for now, will write more about SQL on Linux on coming updates from Microsoft !!!

–> You can also watch this on YouTube:


Advertisements

Install and Configure SQL Server 2017 on Linux Azure VM

January 4, 2017 2 comments

 
SQL-Loves-Linux_2_Twitter-002-640x358

 
In my [previous blog] post we saw how to Create a new Linux VM on Azure. After creating it we connected it from a Windows workstation by using PuYYT tool and tested one Unix command.
 

Here in this post we will see how we can install SQL Server on Linux.

Microsoft is working on a new SQL Server vNext version that will work both on Windows as well as Linux. So with the recent [CTP 1.x] release we can see how does it look like on both the Operating Systems and get some hands on with it.
 

–> Get the Repository and Install SQL Server:

1. Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2. Register the Microsoft SQL Server Ubuntu repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list

3. Re-synchronize the Package index files from their sources, to get information of the newest versions of packages and their dependencies, and finally Install MSSQL-Server package:

sudo apt-get update
sudo apt-get install -y mssql-server

After Install it will ask you to run SQL Server setup by following message:

Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R)

4. Now post install, we need to configure SQL Server and set the SA password, and SQL services.

sudo /opt/mssql/bin/sqlservr-setup

The Configuration gives following prompts in between:

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Setting system administrator (SA) account password…

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y

5. Post Configuration you can verify that the service is running:

systemctl status mssql-server


 

–> Install Tools: This will install the command-line tools (sqlcmd & BCP), Microsoft ODBC drivers, and their dependencies.

6. Re-synchronize the Package index files, and Install MSSQL-Tools:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
sudo ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

 

–> Connect to SQL instance:

9. We will use sqlcmd to connect to SQL Server vNext on our Linux VM. Run the sqlcmd command with following params:
– S: Server Name
– U: User Name
– P: Password

sqlcmd -S localhost -U SA -P ”

 

–> Run SQL Queries:

10. Now after connecting to the SQL Linux instance, let’s test come queries:

We will check the Version of SQL Server and list out system Databases:

select @@version
go

select database_id, name, create_date from sys.databases
go

Output of version statement:
Microsoft SQL Server vNext (CTP1.1) – 14.0.100.187 (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)

11. Let’s do more and Create a new Database, new Table and insert some sample records:

CREATE DATABASE SQLdbOnLinux
go

use SQLdbOnLinux
go
--Changed database context to 'SQLdbOnLinux'.
CREATE TABLE Test (i int, name varchar(25))
go
insert into Test values (1, 'Manoj')
insert into Test values (2, 'Saurabh')
go

--(1 rows affected)

--(1 rows affected)
select * from Test
go
--i           name
------------- -------------------------
--          1 Manoj
--          2 Saurabh

--(2 rows affected)

I think you would get amazed like me after using SQL Server on a Linux environment.

In my [next post] I’ll show how we can connect this SQL Linux instance from a Windows workstation via SSMS !!!

–> You can also watch this video on YouTube:


Spinning up a new Linux VM on Microsoft Azure

January 2, 2017 2 comments

 
First of all a very very Happy New Year to all of you !!!
 

This is my first blog post in 2017, and I’ll start with the most happening thing with SQL Server, which is SQL Server for Linux.
 

So today first of all we will see how to setup a Linux VM on Microsoft Azure cloud. And in next posts I’ve show how can we Install & Setup SQL Server on Linux.

I will use Ubuntu Server 16.04 LTS (Xenial Xerus) version from Azure Marketplace, and show how to setup a Linux VM in a step by step process.

All you need is a Microsoft Azure account, if you have not setup, please do it from here.
 

1. Go to portal.azure.com and login with your credentials.

2. Click on the “+” icon, under “Marketplace” select Compute and under “Featured Apps” select Ubuntu Server.

linux-ubuntu-on-azure-01
 

3. The “Deployment Model” must be showing as Resource Manager by default, if not select form the drop-down list, and click on Create button.

linux-ubuntu-on-azure-02
 

4. Now on the Create virtual machine window, under “1. Basic” section provide:
– Name: new name for your Linux VM
– VM disk type: select Disk type, SSD or HDD
– User name: provide a unique user name for Admin purpose
– Authentication type: I selected “Password”
– Password & Confirm password
– Subscription: will show you your current subscription
– Resource Group: Provide a new name or select from the drop-down list
– Location: select location as per your preference and available in the list.
… and click OK.

linux-ubuntu-on-azure-03
 

5. On “2. Size” section Choose a size for your VM. I selected the DS1_v2 Standard, the cheapest one, finally click on Select button.

linux-ubuntu-on-azure-04
 

6. On 3. Settings section check the properties, I’ve left them as default for now, and click OK:

linux-ubuntu-on-azure-05
 

7. On the “4. Summary” section, the Portal will do various validations, just confirm all the settings and click OK.

linux-ubuntu-on-azure-06
 

8. And after few seconds or minutes your new Ubuntu Linux VM will be created and you can check the new widget appearing on your Dashboard as shown below:

linux-ubuntu-on-azure-07
 

9. Now to get the NIC (network Interface Card) or the User Name & IP address of your VM, click on the widget.

It will open a new window where you can check all the details related to your new Linux VM.

Just click the Connect button at the top which will show you the details so that you can connect via SSH or Telnet.

linux-ubuntu-on-azure-08
 

10. Now to connect the Linux VM you can either:

a. SSH directly from a Mac or Linux Terminal workstation.

b. Or, if you are on a Windows workstation, you need to use PuTTY, MobaXTerm or Cygwin to SSH to the Linux Azure VM.

Here, we will try connecting from a Windows workstation, so we will download PuTTY from www.putty.org
 

The downloaded PuTTY is a simple EXE application, just open it. Here we will provide the IP address that we saw above, you can copy and paste it as shown below and click Open:

linux-ubuntu-on-azure-09
 

11. This will open a PuTTY command shell window which will prompt you to enter the Linux username & password that you set in previous steps, please apply as shown below:

linux-ubuntu-on-azure-10
 

12. After you successfully logged in you will see some welcome message from Ubuntu with other details.

Just to make sure I’m really connected to a Linux command shell, I tried executing a Unix command “ls /” to list all the files & directories in the root drive.

linux-ubuntu-on-azure-11
 

So, this is how you can simply Create or Spin up a Linux VM in few minutes !!!
 

In my [next post] I’ll setup or install SQL Server vNext on this Linux VM and show you how can we connect via SSMS from a Windows workstation.

–> You can also watch this on YouTube:


SQL Server on Linux – Microsoft announcement

March 12, 2016 Leave a comment

SQL-Loves-Linux_2_Twitter-002-640x358
 

Scott Guthrie, Executive Vice President, Cloud and Enterprise Group, Microsoft on March 7, 2016 announced plans to bring SQL Server to Linux. He mentioned that this will enable SQL Server to deliver a consistent data platform across Windows Server and Linux, as well as on-premises and cloud, targeting the availability by mid-2017.

You can sign up for a Private preview here.
 

Recently in a Data Driven event Shawn Bice, General Manager, Database Systems Group, Microsoft showcased the capabilities and new features of SQL Server 2016. While demonstrating he gave a glimpse of SQL Server preview version running on Linux, and you can see the version in the snapshot below:

Microsoft SQL Server (Preview) – 13.0.8000.6 (X64)
Feb 24 2016 22:03:46 2015.0130.8000.06
Copyright (c) Microsoft Corporation
on Linux (Ubuntu 15.10)

SQL on Linux
 

Well I’m very excited and looking forward to download the SQL Server Linux preview and do some hands on.

Check more at https://www.microsoft.com/en-in/server-cloud/sql-server-on-linux.aspx
 


Categories: Linux, Microsoft Tags:

Basic UNIX/Linux commands for Interview Questions – Part 2

April 15, 2010 1 comment

Some basic UNIX/Linux commands (Part-2): [Part-1]
… from college notes !
 

Q36: Display record length of the 1st line of file emp.1st.

Ans: [student@localhost student]$ head -1 emp1.1st |wc –c
41
 

Q37: Display record length of the 1st line of file emp.1st & store result in a variable.

Ans: [student@localhost student]$ p=`head -1 emp1.1st|wc -c`
[student@localhost student]$ echo “$p”
41
 

Q38: Display line no 5 & 7 from emp2.1st

Ans: [student@localhost student]$ head -5 emp2.1st > pp
[student@localhost student]$ tail -1 pp >pp1
[student@localhost student]$ tail -1 emp2.1st >>pp1
[student@localhost student]$ cat pp1
4290|jayant choudhary|executive|production|07/09/50|6000
3564|sudhir agarwal|execute|personnel|06/07/47|7500
 

Q39: Get the year of joining from file emp2.1st of all the emp.

Ans: [student@localhost student]$ cut –d “|” -f 5.7, 5.8 emp2.1st
50
47
58
62
50
55
 

Q40: Create a file shortlist by starting 1st 5 lines of emp1.1st.

Ans: [student@localhost student]$ head -5 emp1.1st | tee shortlist
9876|barun sengupta|director|production|12/03/50|7000
2365|jai sharma|director|personnel|11/05/47|7800
5423|n.k. gupta|chairman|admin|30/08/56|5400
6213|karuna ganguly|g.m|accounts|05/06/62/6300\
4290|jayant choudhary|executive|production|07/09/50|60007
 

Q41: Extract field numbers 2 & 3 from shortlist save as cut1.

Ans: [student@localhost student]$ cut -d”|” -f 2,3 shortlist
barun sengupta|director
jai sharma|director
n.k. gupta|chairman
karuna ganguly|g.m
jayant choudhary|executivem
 

Q42: Extract fields 1,4,5 from shortlist save as cut2.

Ans: [student@localhost student]$ cut -d”|” -f 1,4,5 shortlist >cut2
[student@localhost student]$ cat cut2
9876|production|12/03/50
2365|personnel|11/05/47
5423|admin|30/08/56
6213|accounts|05/06/62/6300\
4290|production|07/09/50
 

Q43: Put all fields of cut1 & cut2 together.

Ans: [student@localhost student]$ paste -d”|” cut1 cut2
a.k. shukla|g.m|9876|production|12/03/50
sumit chakrabarty|d.g.m |2365|personnel|11/05/47
chanchal singhvi | director|5423|admin|30/08/56
s.n dasgupta|manager|6213|accounts|05/06/62/6300\
anil aggarwal|manager|4290|production|07/09/50
 

Q44: Sort on 2nd field of shortlist in two ways.

Ans:
a) [student@localhost student]$ sort -t”|” +1 shortlist
9876|barun sengupta|director|production|12/03/50|7000
2365|jai sharma|director|personnel|11/05/47|7800
4290|jayant choudhary|executive|production|07/09/50|6000
6213|karuna ganguly|g.m|accounts|05/06/62/6300\
5423|n.k. gupta|chairman|admin|30/08/56|5400

b) [student@localhost student]$ sort -t”|” -k2 shortlist
9876|barun sengupta|director|production|12/03/50|7000
2365|jai sharma|director|personnel|11/05/47|7800
4290|jayant choudhary|executive|production|07/09/50|6000
6213|karuna ganguly|g.m|accounts|05/06/62/6300\
 

Q45: Create a sorted output file named sortlist.

Ans: [student@localhost student]$ sort -o sortlist shortlist
[student@localhost student]$ cat shortlist
9876|barun sengupta|director|production|12/03/50|7000
2365|jai sharma|director|personnel|11/05/47|7800
5423|n.k. gupta|chairman|admin|30/08/56|5400
6213|karuna ganguly|g.m|accounts|05/06/62/6300\
4290|jayant choudhary|executive|production|07/09/50|6000
 

Q46: Sort on 3rd field & then by 2nd field of shortlist.

Ans: [student@localhost student]$ sort -t”|” -k 5.7,5.8 shortlist
2365|jai sharma|director|personnel|11/05/47|7800
4290|jayant choudhary|executive|production|07/09/50|6000
9876|barun sengupta|director|production|12/03/50|7000
5423|n.k. gupta|chairman|admin|30/08/56|5400
6213|karuna ganguly|g.m|accounts|05/06/62/6300\
 

Q47: Sort according to year of birth in shortlist.

Ans: [student@localhost student]$ sort -t”|” -k 5.7,5.8 shortlist
2365|jai sharma|director|personnel|11/05/47|7800
4290|jayant choudhary|executive|production|07/09/50|6000
9876|barun sengupta|director|production|12/03/50|7000
5423|n.k. gupta|chairman|admin|30/08/56|5400
6213|karuna ganguly|g.m|accounts|05/06/62/6300\r of birth in shortlist.
 

Q48: Create a file dept.1st.

Ans: [student@localhost student]$ cat >dept.1st
 

Q49: Convert ‘|’ to ‘~’ in emp1.1st.

Ans: [student@localhost student]$ tr ‘|’ ‘~’ <emp1.1st
2233~a.k. shukla~g.m~sales~12/52/52~6000
5678~sumit chakrabarty~d.g.m ~marketing~19/04/43~6000
1006~chanchal singhvi ~ director~sales~03/09/38~6700
1265~s.n dasgupta~manager~sales~12/09/63~5600
2476~anil aggarwal~manager~sales~01/05/59~5000
6521~lalit chowdary~director~marketing~26/09/45~8200
2345~j.b.saxena~g.m~marketing~12/03/45~8000
0110~v.k.agarwal~g.m~marketing~31/12/40~9000
 

Q50: Display frequency of occurance of all the lines.

Ans: [student@localhost student]$ uniq –c
 

Q51: Display lines beginning with 2.

Ans: [student@localhost student]$ grep "^2" emp1.1st
2233|a.k. shukla|g.m|sales|12/52/52|6000
2476|anil aggarwal|manager|sales|01/05/59|5000
2345|j.b.saxena|g.m|marketing|12/03/45|8000
 

Q52: Display lines where salary lie between 7000 & 7999.

Ans: [student@localhost student]$ grep “7…$” emp1.1st
6521|lalit chowdury |director |marketing|26/09/45|8200
2345|j.b.saxena |g.m. |marketing|12/03/45|8000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000
 

Q53: Remove consecutive occurrences of spaces in emp1.1st.

Ans: [student@localhost student]$ tr –s ‘ ‘ <emp.1st
2233|a.k.shukla|g.m.|sales|12/12/52|6000
5678|sumit chakrobarty|d.g.m|marketing|19/04/43|6000
1006|chanchal singhvi|director|sales|03/09/38|6700
1265|s.n.dasgupta|manager|sales|12/09/68|5600
2476|anil aggarwal|manager|sales|01/05/59|5000
6521|lalit chowdury|director|marketing|26/09/45|8200
2345|j.b.saxena|g.m.|marketing|12/03/45|8000
0110|v.k.agarwal|g.m.|marketing|31/12/40|9000
 

Q54: Convert the contents of emp1.1st to uppercase.

Ans: [student@localhost student]$ sort –f emp1.1st
2233|A.K.SHUKLA |G.M. |SALES |12/12/52|6000
5678|SUMIT CHAKROBARTY |D.G.M |MARKETING|19/04/43|6000
1006|CHANCHAL SINGHVI |DIRECTOR |SALES |03/09/38|6700
1265|S.N.DASGUPTA |MANAGER |SALES |12/09/68|5600
2476|ANIL AGGARWAL |MANAGER |SALES |01/05/59|5000
6521|LALIT CHOWDURY |DIRECTO |MARKETING|26/09/45|8200
2345|J.B.SAXENA |G.M. |MARKETING|12/03/45|8000
0110|V.K.AGARWAL |G.M. |MARKETING|31/12/40|9000
 

Q55: Display lines containing jai sharma with a variable.

Ans: [student@localhost student]$ a=grep ‘jai sharma’ emp.1st
0
 

Q56: Display lines containing jai sharma without a variable.

Ans: [student@localhost student]$ grep ‘jai sharma’ emp.1st
 

Q57: Display lines containing ‘Agarwal’ , ‘agarwal’,& ‘aggarwal’ from emp.1st.

Ans: [student@localhost student]$ grep –e “Agarwal” -e “agarwal” –e
“aggarwal”
2476|anil aggarwal |manager |sales |01/05/59|5000
0110|v.k.agarwal |g.m. |marketing|31/12/40|9000
 

Q58: Display one copy of redundant records of file dept.1st.

Ans: [student@localhost student]$ uniq –d dept.1st
01|accounts |6213
02|admin |5423
03|marketing |6521
04|personnel |2365
05|production|9876
06|sales |1006