SQL 2017 on Linux :: Configuration Changes – Default Data Directory

Default location of data files including the log files on SQL Server with Linux goes to /var/opt/mssql/data directory. If you need to change or update your default location to some other directory or a folder, here’s how you can do it:

[aman@linuxserver data]$ pwd
/var/opt/mssql/data
[aman@linuxserver data]$ ls -ltr
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 modellog.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 model.mdf
-rw——- 1 mssql mssql 8388608 Jan 22 20:03 tempdb.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:03 linuxdb.mdf
-rw——- 1 mssql mssql 786432 Jan 22 20:03 msdblog.ldf
-rw——- 1 mssql mssql 15400960 Jan 22 20:03 msdbdata.mdf
-rw-rw—- 1 mssql mssql 8388608 Jan 22 20:09 linuxdb_log.ldf
-rw——- 1 mssql mssql 8388608 Jan 22 20:26 templog.ldf
-rw——- 1 mssql mssql 4194304 Jan 23 00:19 master.mdf
-rw——- 1 mssql mssql 1048576 Jan 23 00:19 mastlog.ldf

New location where all your newer DB’s should be created going forward –
“/var/opt/mssql/data/newdata”

Create a new directory:
[aman@linuxserver data]$ mkdir newdata

Changing the owner and groupto mssql:
[aman@linuxserver newdata]$ sudo chown mssql /var/opt/mssql/data/newdata
[sudo] password for aman:
[aman@linuxserver newdata]$ sudo chgrp mssql /var/opt/mssql/data/newdata

[aman@linuxserver newdata]$ pwd
/var/opt/mssql/data/newdata

Currently no files are available here:
[aman@linuxserver newdata]$ ll
total 0

This is how change the default directory for data/log files for SQL Server and following this command’s execution, you got to restart SQL Service:
[aman@linuxserver newdata]$ sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data/newdata
SQL Server needs to be restarted in order to apply this setting. Please run
‘systemctl restart mssql-server.service’.

[aman@linuxserver newdata]$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 Mon 2018-01-22 20:03:00 PST; 4h 34min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2827 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─2827 /opt/mssql/bin/sqlservr
└─2829 /opt/mssql/bin/sqlservr
[aman@linuxserver newdata]$ sudo systemctl restart mssql-server
[aman@linuxserver newdata]$ 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 2018-01-23 00:38:14 PST; 6s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 12963 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─12963 /opt/mssql/bin/sqlservr
└─12969 /opt/mssql/bin/sqlservr

Let’s now try creating a DB and see where does its data files are getting stored:

[aman@linuxserver newdata]$ sqlcmd -S linuxserver -U sa -p
Password:
1> create database newdb
2> go

1> select * from sysaltfiles
2> go
fileid groupid size maxsize growth status perf dbid name filename
—— ——- ———– ———– ———– ———– ———– ——
1 1 1024 -1 8192 2 0 6 newdb /var/opt/mssql/data/newdata/newdb.mdf
2 0 1024 268435456 8192 66 0 6 newdb_log /var/opt/mssql/data/newdata/newdb_log.ldf

Hope this helps..

Happy learning!

Cheers!
Aman Kharbanda

 

 

Advertisements

SQL 2017 on Linux :: Installation

SQL Server 2017 now runs on Linux. SQL Server 2017 has the same underlying database engine on all supported platforms, including Linux. So many existing features and capabilities operate the same way on Linux.

When you install or upgrade SQL Server, you get the latest version of SQL Server from your configured Microsoft repository.

Repository options

There are two main types of repositories for each distribution:

  • Cumulative Updates (CU): The Cumulative Update (CU) repository contains packages for the base SQL Server release and any bug fixes or improvements since that release. Cumulative updates are specific to a release version, such as SQL Server 2017. They are released on a regular cadence.
  • GDR: The GDR repository contains packages for the base SQL Server release and only critical fixes and security updates since that release. These updates are also added to the next CU release.

Each CU and GDR release contains the full SQL Server package and all previous updates for that repository. Updating from a GDR release to a CU release is supported by changing your configured repository for SQL Server.

Here’s how you can get started with the installation for SQL Server 2017:

[aman@linuxserver ~]$ sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

#1) Respect the privacy of others.
#2) Think before you type.
#3) With great power comes great responsibility.

[sudo] password for aman:
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 232 100 232 0 0 1008 0 –:–:– –:–:– –:–:– 1013
[aman@linuxserver ~]$ sudo yum install -y mssql-server
Loaded plugins: fastestmirror, product-id, rhnplugin, search-disabled-repos, subscription-manager, versionlock
This system is receiving updates from RHN Classic or Red Hat Satellite.
packages-microsoft-com-mssql-server-2017 | 2.9 kB 00:00:00
packages-microsoft-com-mssql-server-2017/primary_db | 8.0 kB 00:00:00
Loading mirror speeds from cached hostfile
* test-customized: yum.domain.com
* test-puppet: yum.domain.com
* test-rhel-base: yum.domain.com
* test-rhel-optional: yum.domain.com
* test-rhel-updates: yum.domain.com
Resolving Dependencies
–> Running transaction check
—> Package mssql-server.x86_64 0:14.0.3015.40-1 will be installed
–> Processing Dependency: gdb for package: mssql-server-14.0.3015.40-1.x86_64
–> Processing Dependency: bzip2 for package: mssql-server-14.0.3015.40-1.x86_64
–> Running transaction check
—> Package bzip2.x86_64 0:1.0.6-13.el7 will be installed
—> Package gdb.x86_64 0:7.6.1-100.el7 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================================
Installing:
mssql-server x86_64 14.0.3015.40-1 packages-microsoft-com-mssql-server-2017 166 M
Installing for dependencies:
bzip2 x86_64 1.0.6-13.el7 test-rhel-updates 52 k
gdb x86_64 7.6.1-100.el7 test-rhel-updates 2.4 M

Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package (+2 Dependent packages)

Total download size: 168 M
Installed size: 173 M
Downloading packages:
(1/3): bzip2-1.0.6-13.el7.x86_64.rpm | 52 kB 00:00:00
(2/3): gdb-7.6.1-100.el7.x86_64.rpm | 2.4 MB 00:00:00
warning: /var/cache/yum/x86_64/7Server/packages-microsoft-com-mssql-server-2017/packages/mssql-server-14.0.3015.40-1.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEYB 00:00:00 ETA
Public key for mssql-server-14.0.3015.40-1.x86_64.rpm is not installed
(3/3): mssql-server-14.0.3015.40-1.x86_64.rpm | 166 MB 00:00:11
——————————————————————————————————————————————————————————————————-
Total 14 MB/s | 168 MB 00:00:11
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
Userid : “Microsoft (Release signing) <gpgsecurity@microsoft.com>”
Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
From : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : bzip2-1.0.6-13.el7.x86_64 1/3
Installing : gdb-7.6.1-100.el7.x86_64 2/3
Installing : mssql-server-14.0.3015.40-1.x86_64 3/3

+————————————————————–+
Please run ‘sudo /opt/mssql/bin/mssql-conf setup’ to complete the setup of Microsoft SQL Server
+————————————————————–+

Verifying : gdb-7.6.1-100.el7.x86_64 1/3
Verifying : mssql-server-14.0.3015.40-1.x86_64 2/3
Verifying : bzip2-1.0.6-13.el7.x86_64 3/3

Installed:
mssql-server.x86_64 0:14.0.3015.40-1

Dependency Installed:
bzip2.x86_64 0:1.0.6-13.el7 gdb.x86_64 0:7.6.1-100.el7

Complete!
[aman@linuxserver ~]$ sudo /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:yes

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server…

The SQL Server End-User License Agreement (EULA) must be accepted before SQL
Server can start. The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388.

You can accept the EULA by specifying the –accept-eula command line option,
setting the ACCEPT_EULA environment variable, or using the mssql-conf tool.
Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
in /var/opt/mssql/log for more information.

Give full control on the directory, to have a seamless setup and then rerun the mssql-conf:
[aman@linuxserver opt]$ sudo chmod -R 777 /var/opt/mssql

[aman@linuxserver data]$ sudo /opt/mssql/bin/mssql-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server…

Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
[aman@linuxserver data]$ 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 Mon 2018-01-22 19:44:59 PST; 1min 19s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 25823 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─25823 /opt/mssql/bin/sqlservr
└─25846 /opt/mssql/bin/sqlservr

Installing Tools required for SQL Server:

[aman@linuxserver data]$ sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 877 0 –:–:– –:–:– –:–:– 881
[aman@linuxserver data]$ sudo yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, product-id, rhnplugin, search-disabled-repos, subscription-manager, versionlock
This system is receiving updates from RHN Classic or Red Hat Satellite.
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 42 kB 00:00:00
Loading mirror speeds from cached hostfile
* test-customized: yum.domain.com
* test-puppet: yum.domain.com
* test-rhel-base: yum.domain.com
* test-rhel-optional: yum.domain.com
* test-rhel-updates: yum.domain.com
Resolving Dependencies
–> Running transaction check
—> Package mssql-tools.x86_64 0:14.0.6.0-1 will be installed
–> Processing Dependency: msodbcsql < 13.2.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
–> Processing Dependency: msodbcsql >= 13.1.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
—> Package unixODBC-devel.x86_64 0:2.3.1-11.el7 will be installed
–> Processing Dependency: unixODBC(x86-64) = 2.3.1-11.el7 for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbcminiS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libnn.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libesoobS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libsapdbS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libtdsS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbctxtS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libtemplate.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbcnnS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbcdrvcfg2S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libmimerS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbcdrvcfg1S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: libodbccr.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: liboplodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Processing Dependency: liboraodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
–> Running transaction check
—> Package msodbcsql.x86_64 0:13.1.9.2-1 will be installed
—> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================================
Installing:
mssql-tools x86_64 14.0.6.0-1 packages-microsoft-com-prod 249 k
unixODBC-devel x86_64 2.3.1-11.el7 test-rhel-updates 55 k
Installing for dependencies:
msodbcsql x86_64 13.1.9.2-1 packages-microsoft-com-prod 4.0 M
unixODBC x86_64 2.3.1-11.el7 test-rhel-updates 413 k

Transaction Summary
=======================================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)

Total download size: 4.7 M
Installed size: 5.6 M
Downloading packages:
(1/4): unixODBC-devel-2.3.1-11.el7.x86_64.rpm | 55 kB 00:00:00
(2/4): unixODBC-2.3.1-11.el7.x86_64.rpm | 413 kB 00:00:00
(3/4): mssql-tools-14.0.6.0-1.x86_64.rpm | 249 kB 00:00:00
(4/4): msodbcsql-13.1.9.2-1.x86_64.rpm | 4.0 MB 00:00:00
——————————————————————————————————————————————————————————————————-
Total 7.1 MB/s | 4.7 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : unixODBC-2.3.1-11.el7.x86_64 1/4
The license terms for this product can be downloaded from
https://aka.ms/odbc131eula and found in
/usr/share/doc/msodbcsql/LICENSE.TXT . By entering ‘YES’,
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : msodbcsql-13.1.9.2-1.x86_64 2/4
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering ‘YES’,
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : mssql-tools-14.0.6.0-1.x86_64 3/4
Installing : unixODBC-devel-2.3.1-11.el7.x86_64 4/4
Verifying : unixODBC-devel-2.3.1-11.el7.x86_64 1/4
Verifying : unixODBC-2.3.1-11.el7.x86_64 2/4
Verifying : mssql-tools-14.0.6.0-1.x86_64 3/4
Verifying : msodbcsql-13.1.9.2-1.x86_64 4/4

Installed:
mssql-tools.x86_64 0:14.0.6.0-1 unixODBC-devel.x86_64 0:2.3.1-11.el7

Dependency Installed:
msodbcsql.x86_64 0:13.1.9.2-1 unixODBC.x86_64 0:2.3.1-11.el7

Complete!
[aman@linuxserver data]$ echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile
[aman@linuxserver data]$ echo ‘export PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc
[aman@linuxserver data]$ source ~/.bashrc
[aman@linuxserver data]$ sqlcmd -S linuxserver -U sa -p
Password:
1> select @@version;
2> go

————————————————————————————————————————————————————————————————————————————————————————————————————
Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) – 14.0.3015.40 (X64)
Dec 22 2017 16:13:22
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Red Hat Enterprise Linux Server 7.4 (Maipo))

Installing SQL Server Agent:

[aman@linuxserver data]$ sudo yum install mssql-server-agent
[sudo] password for aman:
Loaded plugins: fastestmirror, product-id, rhnplugin, search-disabled-repos, subscription-manager, versionlock
This system is receiving updates from RHN Classic or Red Hat Satellite.
Loading mirror speeds from cached hostfile
* test-customized: yum.domain.com
* test-puppet: yum.domain.com
* test-rhel-base: yum.domain.com
* test-rhel-optional: yum.domain.com
* test-rhel-updates: yum.domain.com
Resolving Dependencies
–> Running transaction check
—> Package mssql-server-agent.x86_64 0:14.0.3015.40-1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================================
Installing:
mssql-server-agent x86_64 14.0.3015.40-1 packages-microsoft-com-mssql-server-2017 1.5 M

Transaction Summary
=======================================================================================================================================================================================================
Install 1 Package

Total download size: 1.5 M
Installed size: 1.5 M
Is this ok [y/d/N]: y
Downloading packages:
mssql-server-agent-14.0.3015.40-1.x86_64.rpm | 1.5 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-agent-14.0.3015.40-1.x86_64 1/1

+——————————————————————————–+
Please restart mssql-server to enable Microsoft SQL Server Agent.
+——————————————————————————–+

Verifying : mssql-server-agent-14.0.3015.40-1.x86_64 1/1

Installed:
mssql-server-agent.x86_64 0:14.0.3015.40-1

Complete!
[aman@linuxserver data]$ sudo systemctl restart mssql-server
[aman@linuxserver data]$ 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 Mon 2018-01-22 20:03:00 PST; 14s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2827 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─2827 /opt/mssql/bin/sqlservr
└─2829 /opt/mssql/bin/sqlservr

In similar fashion, we can install Integration Services on SQL Server 2017 for Linux:

sudo yum install -y mssql-server-is
Loaded plugins: fastestmirror, product-id, rhnplugin, search-disabled-repos, subscription-manager, versionlock
This system is receiving updates from RHN Classic or Red Hat Satellite.
packages-microsoft-com-mssql-server-2017 | 2.9 kB 00:00:00
Loading mirror speeds from cached hostfile
* domain-customized: yup.domain.com
* domain-puppet: yup.domain.com
* domain-rhel-base: yup.domain.com
* domain-rhel-optional: yup.domain.com
* domain-rhel-updates: yup.domain.com
Resolving Dependencies
–> Running transaction check
—> Package mssql-server-is.x86_64 0:14.0.3015.40-1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

======================================================================================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================================================================================
Installing:
mssql-server-is x86_64 14.0.3015.40-1 packages-microsoft-com-mssql-server-2017
172 M

Transaction Summary
======================================================================================================================================================================================================
Install 1 Package

Total download size: 172 M
Installed size: 815 M
Downloading packages:
mssql-server-is-14.0.3015.40-1.x86_64.rpm | 172 MB 00:00:09
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-is-14.0.3015.40-1.x86_64 1/1

+——————————————————————————–+
Please run ‘sudo /opt/ssis/bin/ssis-conf setup’
to complete the setup of Microsoft SQL Server Integration Services
+——————————————————————————–+

Verifying : mssql-server-is-14.0.3015.40-1.x86_64 1/1

Installed:
mssql-server-is.x86_64 0:14.0.3015.40-1

Complete!
[aman@linuxserver ~]$ sudo /opt/ssis/bin/ssis-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:YES

Setup has completed successfully.
[aman@linuxserver ~]$ export PATH=/opt/ssis/bin:$PATH

 

Happy learning!

Cheers!
Aman Kharbanda

Database fails to generate a Checkpoint

This seems to be one of the many strange errors within SQL Server that you may encounter. You might’ve lately restored this DB from the server where Replication flag would have been enabled – That’s the closest of the scenarios which happened in my case. Although system DB’s were not restored as part of the exercise.

So, here’s what the error message will scare you with:
One or more recovery units belonging to database ‘mydatabase’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

The log scan number (643555:241:0) passed to log scan in database ‘ mydatabase ‘ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup

Couple of options to start with:

  1. Check within the sys.databases table and see if the log_reuse_wait_desc is marked with Replication. If so, then we must work to get rid of it.
    SELECT name, log_reuse_wait_desc FROM sys.databases where log_reuse_wait_desc = ‘Replication’
  2. You may execute this stored procedure to remove replication from this DB if you’re sure that it’s not a replication participating DB.
    EXEC sp_removedbreplication ‘mydatabasename’
  3. After executing #2, if DB entry is still showing up in sys.databases table within log_reuse_wait_desc as ‘Replication’ then try marking the transactions as replicated using this command:
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
  4. After executing #3, if you’re still hitting up this error, then advise would be to install Replication component using the setup.exe and put the DB into publication (Use any dummy table) and then remove replication using the same command as mentioned in #2.

Off course, prior going into this series of steps you’ll have to verify a certain things respective to your environment:

  • Ensure that database integrity is rightly checked and validated.
  • Error log is rightly checked and there are no more errors available there, apart of this one.
  • Server resources are available in plenty.
  • Clean restoration was performed earlier while bringing this DB online.

Hope this helps!

Happy leaning.

Cheers!
Aman Kharbanda

Why is my transaction log file growing rapidly?

Blog’s headline must have given you a brief idea on the context of this issue.

Many a times this issue comes across us, if the settings are not in correct order and we end up in getting ‘Error: 9002, Severity: 17, State: 2 Transaction Log of XYZ database is full’.

With this error, SQL Server may mark databases as suspect because of a lack of space for transaction log expansion.

Probable causes and adverse effects of this issue
i) A very large transaction log file which can lead the transactions to fail and may start to roll back.
ii)Transactions may take a long time to complete.
iii)Performance issues may occur.
iv)Blocking may occur.

How do I stop it from eating up all my disk space? What should I do?
1) Truncate the inactive transactions in your transaction log by performing an immediate log backup. (The inactive part of the transaction log file contains the completed transactions, so the transaction log file is no longer used by SQL Server during the recovery process. SQL Server reuses this truncated, inactive space in the transaction log instead of letting the transaction log continue to grow and use more space).
2) Shrink the transaction log file (Backup or reducing the size doesn’t really truncate the log, so you have to opt for shrink task).
3) Preventing log file to grow unexpectedly –
    a) Expand the log file size to allow it grow until we have space on the drive.
    b) Configure auto-grow setting (Still need to be careful with the free space on disk).
    c) Consider changing the recovery model to Simple (You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation).

Hope this clarifies.

Cheers!
Aman Kharbanda

Move the DB Files without taking the database into offline mode

Move the DB Files without taking the database into offline mode-

With time when you see the drive on which DB is hosted is running out of space, and you tend to approach the Wintel/Storage teams to add more LUN in order to allocate more disk space or you ask the application teams to housekeep some of their data.

What if there is no such possibility and you are stuck in a situation where movement of files to another bigger drive becomes your ultimate challenge without any downtime. Yes, management hates the word ‘Downtime’ 🙂

We all must have used attach/detach method which involves detaching a database, moving the files, then re-attaching the database. There is another method which involves taking a database offline, running the ALTER DATABASE command to change file locations, moving the files, and bringing the database back online.

Common limitation with these methods – The database has to be offline.

There is another approach to skip such situation – DBCC Shrinkfile (Logical FileName, EmptyFile).

Ceate a new file using the ALTER DATABASE command, then move the data using the DBCC SHRINKFILE command with the EMPTYFILE option.
Secondly, use ALTER DATABASE command to remove the empty file.

Excerpts from BOL-
EMPTYFILE – Migrates all data from the specified file to other files in the same filegroup. Because the Database Engine no longer allows data to be placed in the empty file, the file can be removed by using the ALTER DATABASE statement.

T-SQL:
Use Test;
— Create a data file and assume it contains data.
ALTER DATABASE Test
ADD FILE (
    NAME = Testdata,
    FILENAME = ‘X:\data.ndf’,
    SIZE = 100MB
    )
GO
— Empty the data file.
DBCC SHRINKFILE (Testdata, EMPTYFILE);
GO
— Remove the data file from the database.
ALTER DATABASE Test
REMOVE FILE Testdata;
GO

Simple isn’t it! Hope this helps.

Cheers!
Aman Kharbanda

Shrink DB takes more time?

We have often seen occurrences where DB shrink task takes more time than we actually estimate and think of. Imagine shrink operation is going smoothly when you take a look at it’s progress, and after a span of time say a few hours, when you again look at the percentage value, you come to know that shrink progress has been stalled.

What could have been the reason for this slowness? You would like to see if there are any blocked processes at the back end or any unavoidable reasons for this very very slow progress.

Try to have a look at perfmon and verify the Disk performance counters – Avg. Disk sec/Transfer to understand if there are any disk performance issues (Look for the drives where Data/Log file were hosted) and catch hold of Windows fellows, if there are similar issues.
Criteria suggested by Microsoft is – If Avg. Disk Sec/Transfer is larger than 0.09, then it indicates that we have disk performance issue.

Also, enable the trace and verify what kind of data is your shrink task moving? At times, when Blob data comes into picture, then Shrink Operation is bound to take more time than expected.

Below blog article (by Paul Randal) explain why LOB data makes shrink run slowly. http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/

What to do to get space released to the OS quickly?
Instead of running shrink DB, try to execute DBCC Shrinkfile and look to release space in small chunks. Say, after the application data housekeeping you see that the DB has 40GB of free space, then don’t go directly for 40GB at one shot. Try for 5GB batches and run it in multiple iterations.
Also, Shrink works in batch sizes of ~32 pages so it can be cancelled and only the last batch would be rolled back. This means that we can start it, kill it and restart at a later time.

Best Practices
1) DBCC SHRINKFILE or DBCC SHRINKDATABASE hold very small transactions. Better not to run at the same time with DDL statements like index rebuild, which may require schema lock, and cause waiting.
2) Though DB Shrink is not generally recommended, since it causes a lot of fragmentation and ultimately becomes a major culprit in slowing down your SQL Server.
3) Consider using trace flag -T2548, which will skip the compact step during the shrink. (LOB data are compacted by default during shrink operation).  If we disable the LOB data compaction, the shrink time will be reduced. (Recommendation is to verify the usage in test environment first, before going to PRD Servers).

Hope this helps.

Cheers!
Aman Kharbanda