Report Server DB in a SQL Server Failover Cluster

Failover clustering is supported only for the report server database; you cannot run the Report Server service as part of a failover cluster.

To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database in the Database Setup page of the Reporting Services Configuration tool.

Although the Report Server service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.

Hope this helps!

Happy learning!

Cheers,
Aman Kharbanda

Advertisements

Limitations for Microsoft SQL Server if used on AWS as their RDS Service

So before you plan on moving your workloads from On-Prem SQL Server to the cloud, if your preferred public cloud vendor is going to be AWS and you wish to use their DBaaS i.e RDS then here are a few limitations, as of this writing, that you may encounter with SQL Server.
As always and with every cloud vendor, there are product updates almost every day, refer to their website for more up to date changes.

Limits for Microsoft SQL Server DB Instances

The Amazon RDS implementation of Microsoft SQL Server on a DB instance have some limitations you should be aware of:

  • You can create up to 30 databases on each of your DB instances running Microsoft SQL Server. The Microsoft system databases, such as master and model, don’t count toward this limit.
  • Some ports are reserved for Amazon RDS use and you can’t use them when you create a DB instance.
  • Amazon RDS for SQL Server does not support importing data into the msdb database.
  • You can’t rename databases on a DB instance in a SQL Server Multi-AZ with Mirroring deployment.
  • The maximum storage size for SQL Server DB instances is the following:
    • General Purpose (SSD) storage: 16 TB for all editions
    • Provisioned IOPS storage: 16 TB for all editions
    • Magnetic storage: 1 TB for all editions

    If you have a scenario that requires a larger amount of storage, you can use sharding across multiple DB instances to get around the limit. This approach requires data-dependent routing logic in applications that connect to the sharded system. You can use an existing sharding framework, or you can write custom code to enable sharding.

  • The minimum storage size for SQL Server DB instances is the following:
    • General Purpose (SSD) storage: 200 GB for Enterprise and Standard editions, 20 GB for Web and Express editions
    • Provisioned IOPS storage: 200 GB for Enterprise and Standard editions, 100 GB for Web and Express editions
    • Magnetic storage: 200 GB for Enterprise and Standard editions, 20 GB for Web and Express editions
  • Because of limitations in Microsoft SQL Server, restoring to a point in time before successful execution of a DROP DATABASE might not reflect the state of that database at that point in time. For example, the dropped database is typically restored to its state up to 5 minutes before the DROP DATABASE command was issued, which means that you can’t restore the transactions made during those few minutes on your dropped database. To work around this, you can reissue the DROP DATABASE command after the restore operation is completed. Dropping a database removes the transaction logs for that database.

Happy learning!

Cheers!
Aman Kharbanda

Workaround to get SQL Server’s Developer Edition on Amazon RDS

Amazon RDS doesn’t support running SQL Server Analysis Services, SQL Server Integration Services, SQL Server Reporting Services, Data Quality Services, or Master Data Services on the same server as your Amazon RDS DB instance. To use these features, the recommended way forward is that you install SQL Server on an Amazon EC2 instance, or use an on-premise SQL Server instance, to act as the Reporting, Analysis, Integration, or Master Data Services server for your SQL Server DB instance on Amazon RDS. You can install SQL Server on an Amazon EC2 instance with Amazon EBS storage, pursuant to Microsoft licensing policies.

Because of licensing requirements, AWS can’t offer SQL Server Developer edition on Amazon RDS. You can use Express edition for many development, testing, and other nonproduction needs. However, if you need the full feature capabilities of an enterprise-level installation of SQL Server, you must use a dedicated host environment. You can download and install SQL Server Developer edition (and other MSDN products) on Amazon EC2. Dedicated infrastructure is not required for Developer edition. By using your own host, you also gain access to other programmability features that are not accessible on Amazon RDS.

Hope this helps while you plan to host your SQL Server in Amazon world!

Happy Learning!

Cheers!
Aman Kharbanda

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

 

 

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

A/B testing solution for SQL Server upgrades

Many of you might’ve come across a situation wherein you’re planning for a SQL Server upgrade, and at the same time, skeptical of the performance/throughout that you’re going to get from this upgraded version of database.

Database Experimentation Assistant (DEA) is a new A/B testing solution for SQL Server upgrades. It will assist in evaluating a targeted version of SQL for a given workload. Customers who are upgrading from previous SQL Server versions (SQL Server 2005 and above) to any new version of the SQL Server will be able to use these analysis metrics provided, such as queries that have compatibility errors, degraded queries, query plans, and other workload comparison data, to help them build higher confidence, making it a successful upgrade experience.

Current available version (As of this writing) is 2.1.51169.2

Screen Shot 2018-01-22 at 11.49.54 AM.png

Supported sources and target versions

Source: SQL Server 2005 and above
Target: SQL Server 2005 and above
Analysis: SQL Server 2008 and above

Happy learning!

Cheers!
Aman Kharbanda

Microsoft® Data Migration Assistant v3.3

Data Migration Assistant (DMA) enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server.

Here’s the download link

What is new in V3.3?

  • DMA v3.3 enables migration of an on-premises SQL Server instance to SQL Server 2017, on both Windows and Linux.

Features:

 

  • Migration blocking issues:  DMA discovers the compatibility issues that block migrating on-prem SQL Server database(s)s to Azure SQL Database(s). It then provides recommendations to help customers remediate those issues.
  • Partially or unsupported features:  DMA detects partially or unsupported features that are currently in use at the source SQL Server. It then provides comprehensive set of recommendations, alternative approaches available in Azure and mitigating steps so that customers can plan ahead this effort into their migration projects.
  • Discovery of issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues categorized under these areas:
    • Breaking changes
    • Behavior changes
    • Deprecated features
  • Discover new features in the target SQL Server platform that the database can benefit from after an upgrade. These are described as feature recommendations and are categorized under these areas:
    • Performance
    • Security
    • Storage

Supported source and target versions

  • Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016
  • Target: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, and Azure SQL Database

Screen Shot 2018-01-05 at 11.39.44 AMScreen Shot 2018-01-05 at 11.40.18 AMScreen Shot 2018-01-05 at 11.42.28 AM

 

Happy learning!

Cheers!
Aman Kharbanda