How can we install MS SQL SERVER local instance on Ubuntu 20.04 machine?
Step 1: Install MS SQL SERVER
1.1 Update Ubuntu system
sudo apt-get update
sudo apt-get -y upgrade
sudo reboot (only if kernel updates done)
1.2 Import the public repository GPG keys:
wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
1.3 Register the Microsoft SQL Server Ubuntu repository for SQL Server 2019 (choose based on your ubuntu version):
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
1.4 Commands to install MS SQL Server instance locally
sudo apt-get update
sudo apt-get install -y mssql-server (-y to install silently using default option without prompt for user confirmation)
1.5 Configure the SQL instance
sudo /opt/mssql/bin/mssql-conf setup
1.6 Check the mssql-server service (deamon) is running
systemctl status mssql-server --no-pager
1.7 Want to connect this SQL server instance from other machine make sure to open port 1433
How can we install MS SQL SERVER local instance on Linux 2 machine?
Note: Instead of 'apt' you can use 'yum' in Linux 2 edition
Step 1: Update Linux 2 System
sudo yum update
Step 2: Add install MS SQL into yum repository
sudo curl https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo -o /etc/yum.repos.d/mssql-server-2019.repo
sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo -o /etc/yum.repos.d/msprod.repo
Step 3: You can verify above contents added in below files
$ cat /etc/yum.repos.d/mssql-server-2019.repo
$ cat /etc/yum.repos.d/msprod.repo
Step 4: Install MSSQL SERVER & TOOLS
sudo yum -y install mssql-server mssql-tools unixODBC-devel
Step 5: Setup MS SQL SERVER (Choose DEV/EXP/STD, PASSWORD)
sudo /opt/mssql/bin/mssql-conf setup
Step 6: Check MS SQL SERVER running
sudo systemctrl status mssql-server
Note: To open port 1433 in 1) EC2 security group in AWS 2) Networking => Inbound in Azure Virtual Machine (AVM)
How to copy database backup (.back) from local to EC2 instance through SSH command?
Syntax
$ scp -r -i <<keyname>> <<LocalFileNameWithPath>> <<username>>@<<publicDNS>>:<<EC2 path>>
Example
$ scp -r -i testmachinekey.pem employee.bak This email address is being protected from spambots. You need JavaScript enabled to view it.:/home/ec2-user/backup
How to restore a database backup from local machine to EC2 Linux instance?
1) Make sure MS SQL port 1433 opened on EC2 Instance
Select EC2 instance => Security Group => Inbound => Edit => Add MSSQL (1433)
2) Create a Backup folder in EC2 instance
2.1) Connect to EC2 instance
$ ssh -i "testmachinekey.pem" This email address is being protected from spambots. You need JavaScript enabled to view it.
2.2) Make directory to keep backup and data
$ sudo mkdir backup (inside "/opt/mssql/")
$ sudo mkdir data (inside "/opt/mssql/")
2.3) Copy DB backup from <<LOCAL MACHINE>> to EC2 instance
In bash command prompt (don't connect to EC2 instance) run below command and make sure .pem file in the same folder where backup (.bak) resides
scp -r -i testmachinekey.pem employee.bak ec2-user@ec2-14-121-5x-XXX.ap-southeast-1.compute.amazonaws.com:/opt/mssql/backup/.
scp -r -i testmachinekey.pem inventory.bak ec2-user@ec2-14-121-5x-XXX.ap-southeast-1.compute.amazonaws.com:/opt/mssql/backup/.
3)Restore DB
3.1) Connect EC2 SQL Server instance from you local SQL Server management studio using its public IP
3.2) Database => Restore => select backup from path "opt\mssql\bakcup\employee.bak
3.3) Database => Restore => select backup from path "opt\mssql\bakcup\inventory.bak
What are the various ways to connect SQL server?
Azure Data Studio => A cross-platform GUI database management utility.
Visual Studio Code => A cross-platform GUI code editor that run Transact-SQL statements with the mssql extension.
PowerShell Core => A cross-platform automation and configuration tool based on cmdlets.
mssql-cli => A cross-platform command-line interface for running Transact-SQL commands.
How do we setup mssql-cli?
1) Import the public repository GPG keys.
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
2) Register the Microsoft Ubuntu repository.
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
3) Installation command with the unixODBC developer package
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev
4) Set Environment path
Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile (for login sessions)
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc (for non-login sessions)
How do we connect to MS SQL SERVER thru MSSQL command line utility?
sqlcmd -S localhost -U SA -P '<YourPassword>'
CREATE DATABASE Employee
SELECT Name from sys.Databases
GO
Note: Both CREATE & SELECT statement executes only after givein "GO" command
Can you show CRUD operations in MS SQL SERVER using command line utility?
1) Create Database
CREATE DATABASE Employee
SELECT Name from sys.Databases
GO
2) Create Table
USE Emp
CREATE TABLE Emp (Empid INT, name NVARCHAR(50), Salary DECIMAL(8,2))
INSERT INTO Emp VALUES (1000, 'Raja', 150000);
INSERT INTO Emp VALUES (1001, 'Prajith', 180000);
GO
3) Select data
SELECT * FROM Emp WHERE Empid > 1000;
GO
4) EXIT SQL command prompt
QUIT
