Uninstall mssql  from cenetos server


                        1) check os version
                        2) mssql installed version
                        3) remove mssql version
                        4) check mssql version is installed

Introduction

This article is about how to proceed when you get error message 'Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'.. '

Problem Definition

SA Password is entered wrong more than three times then SA account will be locked or someone wants to reset the password. 

Solution

Using mssql-conf setup  need following step:- (this require reboot SQL Server)

1. Conf setup

Yyou need to stop mssql-server  using this command   -conf setup command 

sudo systemctl stop mssql-server

sudo /opt/mssql/bin/mssql-conf setup

• Setting up Microsoft SQL Server  
• Enter the new SQL Server system administrator password:   --Enter strong password here  
• Confirm the new SQL Server system administrator password: --Enter strong password here  
• Starting Microsoft SQL Server...  
• Enabling Microsoft SQL Server to run at boot...  
• Setup completed successfully.
• Check Mssql services status using command sudo systemctl status mssql-server

 2. Change password using sp_password 

• Login with User who having sysadmin access on the server.
•  syntax: sp_password NULL, ‘<insert_new_password_here>’, ’sa’ 
• command :

sp_password NULL, 'Mssql@12345', 'sa'

 

 3. Using GUI using Window server:​

Install new version of SSMS   -->SSMS 17.4 is the latest version of SQL Server Management Studio.

   a. Connect Linux SQL server on Windows server using SSMS.
   b. Select  the Security->Logins folder on the left side of your window; Right-click “sa” and choose properties;
 

c.Change password and confirm with complexity. 
   orignal article

MSSQL Server for Linux: Troubleshooting

Introduction:

Normally there is issue or user error see in MSSQL, we check using operating system.Dba should aware of some Linux command.

Commands:

We need to check whether SQL services is running which process id and CPU utilization and read error log.

1.check SQL error log 

pwd:-cd /var/opt/mssql/log

2.list error log file in MSSQL

# ls err* ----

errorlog errorlog.10 errorlog.12 errorlog.14 errorlog.16 errorlog.18 errorlog.2 errorlog.21 errorlog.23 errorlog.3 errorlog.5 errorlog.7 errorlog.9

errorlog.1 errorlog.11 errorlog.13 errorlog.15 errorlog.17 errorlog.19 errorlog.20 errorlog.22 errorlog.24 errorlog.4 errorlog.6 errorlog.8

3.head command

# head -50 errorlog ---> Display 50 lines from errorlog

2017-08-15 20:28:43.67 Server Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)

  Apr 13 2017 11:44:40

  Copyright (C) 2017 Microsoft Corporation. All rights reserved.

  Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

2017-08-15 20:28:43.67 Server UTC adjustment: 5:30

2017-08-15 20:28:43.67 Server (c) Microsoft Corporation.

2017-08-15 20:28:43.68 Server All rights reserved.

2017-08-15 20:28:43.68 Server Server process ID is 4120.

2017-08-15 20:28:43.68 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.

2017-08-15 20:28:43.68 Server Registry startup parameters:

  -d /var/opt/mssql/data/master.mdf

  -l /var/opt/mssql/data/mastlog.ldf

  -e /var/opt/mssql/log/errorlog

2017-08-15 20:28:43.69 Server SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

2017-08-15 20:28:43.70 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2017-08-15 20:28:43.70 Server Detected 3194 MB of RAM. This is an informational message; no user action is required.

2017-08-15 20:28:43.70 Server Using conventional memory in the memory manager.

2017-08-15 20:28:44.03 Server Buffer pool extension is already disabled. No action is necessary.

2017-08-15 20:28:44.26 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.

2017-08-15 20:28:44.26 Server Implied authentication manager initialization failed. Implied authentication will be disabled.

2017-08-15 20:28:44.28 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-SHA384'].

2017-08-15 20:28:44.34 Server The maximum number of dedicated administrator connections for this instance is '1'

2017-08-15 20:28:44.34 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

2017-08-15 20:28:44.35 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

2017-08-15 20:28:44.36 Server In-Memory OLTP initialized on lowend machine.

2017-08-15 20:28:44.54 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

2017-08-15 20:28:44.55 Server Query Store settings initialized with enabled = 1,

2017-08-15 20:28:44.56 spid6s Starting up database 'master'.

2017-08-15 20:28:44.56 Server Software Usage Metrics is disabled.

2017-08-15 20:28:45.13 spid6s 10 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.

2017-08-15 20:28:45.24 spid6s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.

2017-08-15 20:28:45.25 spid6s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

2017-08-15 20:28:45.46 spid6s Buffer pool extension is already disabled. No action is necessary.

2017-08-15 20:28:45.47 spid6s Resource governor reconfiguration succeeded.

2017-08-15 20:28:45.47 spid6s SQL Server Audit is starting the audits. This is an informational message. No user action is required.

2017-08-15 20:28:45.47 spid6s SQL Server Audit has started the audits. This is an informational message. No user action is required.

2017-08-15 20:28:45.65 spid6s SQL Trace ID 1 was started by login "sa".

2017-08-15 20:28:45.69 spid6s Server name is 'DBA04'. This is an informational message only. No user action is required.

4.check sql server is running or not

# sudo systemctl status mssql-server

 mssql-server.service - Microsoft SQL Server Database Engine

  Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)

  Active: active (running) since Tue 2017-08-15 20:28:31 IST; 1 day 12h ago

  Docs: https://docs.microsoft.com/en-us/sql/linux

 Main PID: 56656 (sqlservr)

  CGroup: /system.slice/mssql-server.service

  ├─56656 /opt/mssql/bin/sqlservr

  └─56699 /opt/mssql/bin/sqlservr

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.20 spid6s ....

Aug 15 20:28:47 DBA04 sqlservr[56656]: 2017-08-15 20:28:47.21 spid6s ....

Aug 15 20:28:48 DBA04 sqlservr[56656]: 2017-08-15 20:28:48.78 spid9s ....

Aug 15 20:28:49 DBAT04 sqlservr[56656]: 2017-08-15 20:28:49.32 spid9s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.36 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.36 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.44 spid24s ....

Aug 15 20:28:49 DBA04 sqlservr[56656]: 2017-08-15 20:28:49.62 spid6s ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ....

Aug 15 20:28:55 DBA04 sqlservr[56656]: 2017-08-15 20:28:55.21 Logon ...]

Hint: Some lines were ellipsized, use -l to show in full.

5.process running MSSQL

# ps -ef| grep mssql

root 48427 48368 0 08:50 pts/0 00:00:00 grep --color=auto mssql

mssql 56656   1 0 Aug15 ? 00:00:00 /opt/mssql/bin/sqlservr

mssql 56699 56656 1 Aug15 ? 00:23:26 /opt/mssql/bin/sqlservr

56699 is process id running for mssql.

6.top command

--> show information like tasks,memory,cpu and swap. 

# top

top - 09:29:24 up 119 days, 21:47, 3 users, load average: 0.03, 0.21, 0.30

Tasks: 238 total, 1 running, 235 sleeping, 2 stopped, 0 zombie

%Cpu(s): 0.4 us, 0.3 sy, 0.0 ni, 95.3 id, 3.9 wa, 0.0 hi, 0.0 si, 0.0 st

KiB Mem : 4088260 total, 73712 free, 1553332 used, 2461216 buff/cache

KiB Swap: 8191996 total, 8033108 free, 158888 used. 1353632 avail Mem

  PID USER PR NI VIRT RES SHR S %CPU %MEM   TIME+ COMMAND

42936 oracle -2 0 1488876 16912 14236 S 2.0 0.4 53:54.96 ora_vktm_magic

56699 mssql 20 0 4069136 536168 2640 S 1.3 13.1 23:50.96 sqlservr

42994 oracle 20 0 1489488 41560 38508 S 1.0 1.0 0:36.71 ora_mmnl_magic

49435 root 20 0 157840 2380 1564 R 0.7 0.1 0:00.03 top

57044 mongod 20 0 1078456 37520 2152 S 0.7 0.9 61:40.10 mongod

 3274 root 20 0 0 0 0 S 0.3 0.0 0:00.97 kworker/3:1

  1 root 20 0 191004 2920 1424 S 0.0 0.1 27:26.09 systemd

  2 root 20 0 0 0 0 S 0.0 0.0 0:00.03 kthreadd

  3 root 20 0 0 0 0 S 0.0 0.0 1:52.46 ksoftirqd/0

  5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H

  7 root rt 0 0 0 0 S 0.0 0.0 0:08.03 migration/0

  8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh

CPU utilization is highlighted in yellow color.

7. grep command

-->The grep command searches the given input files for lines containing a match or a text string. Following example where find process which is word related MSSQL.

ps -ef | grep mssql mssql    36817     1  0 Jul28 ?        00:00:00 /opt/mssql/bin/sqlservr mssql    36851 36817  1 Jul28 ?        04:46:58 /opt/mssql/bin/sqlservr root     55572 55513  0 19:54 pts/0    00:00:00 grep --color=auto mssql

8.Check mssql services packages is installed 

# ps -ef | grep mssql

  mssql    36817     1  0 Jul28 ?        00:00:00 /opt/mssql/bin/sqlservr

  mssql    36851 36817  1 Jul28 ?        04:46:58 /opt/mssql/bin/sqlservr

  root     55572 55513  0 19:54 pts/0    00:00:00 grep --color=auto mssql

9. check package information of mssql

# yum info mssql-server

   Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

 * base: centos.mirror.net.in

 * epel: kartolo.sby.datautama.net.id

 * extras: centos.excellmedia.net

 * updates: centos.excellmedia.net

Installed Packages

Name        : mssql-server

Arch        : x86_64

Version     : 14.0.900.75

Release     : 1

Size        : 870 M

Repo        : installed

From repo   : packages-microsoft-com-mssql-server

Summary     : Microsoft SQL Server Relational Database Engine

License     : Commercial

Description : The mssql-server package contains the Microsoft SQL Server Relational Database Engine.

10.  Check file system 

mount | grep "^/dev" --check file system must be XFS or EXT4. 

 

11.Check ip of server

ifconfig

SSMS 17.x is the latest generation of SQL Server Management Studio and provides support for SQL Server 2017.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

SSMS can be installed in the following languages:

SQL Server Management Studio 17.5:
Chinese (People's Republic of China) | Chinese (Taiwan) | English (United States) | French | German | Italian | Japanese | Korean | Portuguese (Brazil) | Russian | Spanish2

SQL Server Management Studio 17.5 Upgrade Package (upgrades 17.x to 17.5):
Chinese (People's Republic of China) | Chinese (Taiwan) | English (United States) | French | German | Italian | Japanese | Korean | Portuguese (Brazil) | Russian | Spanish

 

       Take backup with password and multiple file for faster speed

                       Backup:

                  BACKUP DATABASE [Leave_Management] TO
'                  DISK = 'E:\Leave_Management_file1.bak',
                      DISK = 'F:\Leave_Management_file2.bak'
                     WITH MEDIAPASSWORD='Securepassword'

                      filelist for verification:-

          RESTORE FILELISTONLY FROM DISK = 'E:\Leave_Management_file1.bak',
          DISK = 'F:\Leave_Management_file2.bak'
          WITH MEDIAPASSWORD='Securepassword'

                      
           Restoration for multiple files with password:

          RESTORE database [Leave_Management] FROM DISK = 'E:\Leave_Management_file1.bak',
           DISK = 'F:\Leave_Management_file2.bak'
           WITH MEDIAPASSWORD='Securepassword',
            MOVE 'Leave_Management' TO 'C:\Program Files\Microsoft SQL                  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Leave_Management.mdf',
           MOVE 'Leave_Management_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Leave_Management_log.ldf'

Putty use for caputre screen output

Putty is used for take remote linux machine from window machine.

installation on centos:-

Requirement:

• You need at least 3.25GB of memory to run SQL Server on Linux.
• SQL Server Engine has been tested up to 1 TB of memory at this time. The file system must be XFS or EXT4. Other file systems, such as BTRFS, are unsupported. 

                mount | grep "^/dev"       --check file system should             

• you need  administrative access for installation.

            sudo -l -U msdba   -->User msdba is not allowed to run sudo on servername

Login with superuser

Sudo su 

#cat /etc/centos-release  --check version

     CentOS Linux release 7.3.1611 (Core)

 copy repo file in  /etc/yum.repos.d/ directory only using winscp. 

Install mssql using yum install.  

sudo yum install -y mssql-server

Configuration and setting SA password with strong password.

sudo /opt/mssql/bin/mssql-conf setup
















 Check mssql services  packages is installed 

  root #rpm -qa | grep mssql
















Check mssql services is running.





  root # systemctl status mssql-server
















Enable firewall rule  





sudo firewall-cmd --zone=public --add-port=1433/tcp  --permanentsudo firewall-cmd --reload   









Install mssql agent 







sudo yum install mssql-server-agent

sudo systemctl restart mssql-server














remove old version






sudo yum update

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel














 Install mssql tools with unixODBC developer packages. 











sudo yum update

sudo yum install mssql-tools unixODBC-devel














 Login in mssql using Sqlcmd






[root]# sqlcmd -S 10.16.1.54 -U sa

Password:

1> select @@version

2> go

Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)         Apr 13 2017 11:44:40         Copyright (C) 2017 Microsoft Corporation. All rights reserved.  Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))







check packgae information of mssql






# yum info mssql-server