All Database Sizes in SQL SERVER

DB sizes

 SELECT d.name, (ROUND(SUM(cast(mf.size as bigint)) * 8 / 1024, 0))/1024 Size_GB FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4 -- Skip system databases GROUP BY d.name ORDER BY 2 desc

Adding a Server to Central Management Server(CMS)

-- to know the group name

--- to know the server group. 

select * from msdb.dbo.sysmanagement_shared_server_groups_internal ORDER BY 1 desc 

where name like 'groupname%'

select SERVERNAME  from SERVERLIST_PATCH

-- adding Servers into CMS 

SET NOCOUNT ON  

  declare @name sysname declare @sql nvarchar(max)  

  declare db_cursor cursor for select  distinct(   a.servername + ','  +convert(varchar(50),a.PortNumber))   FROM  tablename a

--order by a.tablename asc 

   open db_cursor 

    fetch next from db_cursor into @name 

--declare @cnt int =0

----set @cnt =0

    while @@FETCH_STATUS = 0 

    begin     

    Set @sql = '

DECLARE @CurrentServerGroup INT

INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers_internal

  (Server_Group_ID, name, Server_name, description, Server_Type)

  SELECT 6'+','''+ LTRIM(RTRIM(@name)) +''''+','''+  LTRIM(RTRIM(@name)) +''''+','''+  LTRIM(RTRIM(@name)) +''''+','+ '0'

    print @sql     

execute(@sql)

     fetch next from db_cursor into @name 

--print @cnt

end  

     close db_cursor 

     deallocate db_cursor

Linux basic commands for SQL SERVER DBA's

Linux basic commands for SQL SERVER DBA's

As Microsoft started supporting SQL SERVER on other operating systems like  Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server (SLES), and Ubuntu.

 As a SQL SERVER DBA we must know some basics of to support other operating systems. 

Sudo -  

superuser do or switch user do. it allows a user with proper permissions to execute a command as another user, such as super user.

It is same like as our Run as administrator in windows level. or Local Admin to the box.

Example Sudo "username "

sudo -V | -h | -l | -L | -v | -k | -K | -s | [ -H ] [-P ] [-S ] [ -b ] | 
     [ -p prompt ] [ -c class|- ] [ -a auth_type ] [-r role ] [-t type ] 
     [ -u username|#uid ] command

Curl 

Curl is a free and open command line tool for transferring files on protocols like Http, Https,FTP,FTPS, SCP, SFT, LDAT, or File. 

You can refer the url

https://curl.haxx.se/

Yum 

Yum stands for "Yellodog Updater Modified". It is an interactive rpm-based package Manager. It can automatically perform system updates,including dependency analysis and obsolete processing based on "repository" Medadata. 

yum [options] [command] [package ...]

SQL SERVER 2017 Installation on Red Hat Linux Environment

SQL SERVER 2017 Installation on  Red Hat Linux Environment

To work with Microsoft Virtual Labs - Visit - https://www.microsoft.com/handsonlabs/selfpacedlabs

SQL server 2017 Installation can be performed with 5 easy steps. 

1. Download the SQL SERVER Package.

2. Install the SQL SERVER Package. 

3. Configure SQL server

4.  Install SQL SERVER Client Tools.
5.  To configure the Environment Variables. 

Steps to Install SQL SERVER 2017 

1. Download Microsoft SQL Server 2017 Red Hat repository configuration file

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
 for SQL SERVER 2019 download Run the following command.

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/

2. To Install SQL SERVER 2017 run the following command. 

sudo yum install -y mssql-server

3. Configure SQL server, run the following Command.

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

4. Download and Installation of SQL SERVER Client tools.

downloading Client tools

sudo curl -o /etc/yum.repos.d/msprod.repo
https://packages.microsoft.com/config/rhel/7/prod.repo




Installation of Client tools. 

sudo yum install -y mssql-tools unixODBC-devel

5. Setting up the environment variables. 

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

To create  Database on Linux environment. 

1. To connect to SQL Server, type the following command, and then press Enter:

sqlcmd -S localhost -U sa -P 'Password'

2. Type the following command, and then press Enter:

CREATE DATABASE Worldwide1

To Check the Version of the Server. 

how to capture a host Ip address through T-SQL

We can capture host address of a server through T_SQL using below script. 

DECLARE @ipaddress varchar(max)

DECLARE @ipaddressLine varchar(max)

DECLARE @pos int

SET nocount on

SET @ipaddress = NULL

Create table #temp_ip (ipLine varchar(200))

INSERT #temp_ip EXEC master..xp_cmdshell 'ipconfig'

SELECT top 1 @ipaddressLine = ipLine FROM #temp_ip

WHERE upper (ipLine) like '%IPV4 ADDRESS%' or upper (ipLine) like '%IP ADDRESS%'

    IF (isnull (@ipaddressLine,'***') != '***')

      BEGIN

       SET @pos = CharIndex (':',@ipaddressLine,1);

       SET @ipaddress = rtrim(ltrim(substring (@ipaddressLine , @pos + 1 ,len (@ipaddressLine) - @pos)))

      END

PRINT @ipaddress

DROP TABLE #temp_ip

SET nocount off

SQL QUERIES FOR INTEROUST

SQL SERER 2014 Upgrade Steps