Showing posts with label Linux. Show all posts
Showing posts with label Linux. Show all posts

Wednesday, February 28, 2018

Offline installation of SQL Server 2017 on Linux

SQL Server is now compatible with different Linux distributions, and there is a lot of excitement about it.
How ever one of the main questions when ever you say SQL Server 2017 is now supported in Linux is, Have you test it? how do you install it?

By looking in the web you can find a lot of post and videos related to it, however most of them are based with Azure VM's (one great post I found is from a good friend that you can find here) or there is also the assumption that you will have an internet connection from your server, but what if not? What if you are a financial institution that is PCI-DSS compliant and the direct connection to the web is blocked or simply your network administrator doesn't want to allow you access to the Microsoft repositories from the network your server is running or you created your VM and the connection is not shared with your host? 

Well, let me tell you there is an option, you are not deemed to fight against the network engineers to enable you the access and here I'll show you the steps.

Pre-requesites:
  1. A machine (VM, physical, etc) with Linux 
    1. Red Hat Enterprise Linux (7.3 or 7.4)
    2. SUSE Linux Enterprise Server (v12 SP2)
    3. Ubuntu (16.04)
    4. Docker (1.8+)
  2. The latest version of SQL Server 2017 downloaded you can get it here and copied onto your Linux machine.
  3. A user to run the install (different than root)
Installation Steps
  1. Connect to your Linux machine. You can use Putty or the SSH client of your preference 
  2. Locate the folder where you have the installer, in my case I put it on "/home/SQLInstall/
  3. execute this command: Sudo yum localinstall <name of the installer> in my case the command looked like Sudo yum localinstall mssql-server-14.0.322.28-2.x86_64.rpm .After executing the command it will ask for the password of the user you are connected with to confirm the permissions and the execution command, then it will start the examination of the media and validate all the dependencies, that will look just like this:
 

   4. If you look at the bottom line, it stops at the Question if the size of the package and the size  matches, if the software is the one you would like to install etc, if everything is correct you will then need to Write the letter "y" to confirm.
   5. After confirming and hitting enter, the process will start to deploy the files in the system, however you will need to pay attention to the following message:

It says "complete!" but there is a remark "Please run 'sudo.... to complete the setup...' " so go and execute the command, otherwise SQL Server won't be installed as it only exported the files but didn't actually installed the engine and you will see why in the next step.

   6.  After executing that command, it will ask you to choose the version you would like to install

   So make sure to enter the right version, In my case I choose 2, as it won't support a production environment and is only for demo purposes.

   7.  After selecting the version, it will ask you to agree with the license terms and you will need to write "Yes" if you accept it or "No" if you don't.

   8.  Then it will ask you to configure the "sa" password.


   9.  After configuring the password the installation will succeed. However one of the problems you will face after getting it installed, is that you can't connect from outside of the server itself, so what you need to do is to enable the port through the firewall, and you can do that by running the following commands:
                   sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
                   sudo firewall-cmd --reload

The first command opens the port 1433 and the second command restarts the firewall service (sort of speak)

  10.  After doing all of the above steps and rebooting the server, you should be able to connect to your SQL Server instance, Congratulations!


Hope this steps lets you get up and running and in the case you run into any issue, let me know and I'll be glad to help and learn with you.

Thanks for reading!

P.D.: If by any chance you don't know how to get Red Hat installed, I'm preparing another post with those steps.

Wednesday, October 11, 2017

What's new in SQL 2017

SQL Server 2017 became General Availability (GA) on 10/2, I've been sharing through the different social media channels the different features and enhancements included in this new version, however I want to consolidate in this posts most of those that you will be seeing in most of the production scenarios with an explanation of each one.

  1. Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. This is a great improvement specially with index operations where you run out of space or have to pause it due to performance degradation, you can later resume where you left it instead of having to cancel, wait the rollback and then start from the beginning.
  2. A new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. For this first version of the adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions. See Adaptive query processing in SQL databases.
  3. Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems. This feature in particular is one of the few Azure first, you can see this included in the Azure SQL database since at least V12 (current), for more reference See Automatic tuning.
  4. sys.dm_os_sys_info has three new columns: socket_count, cores_per_socket, numa_node_count. This in particular helps you check if your current setup exceeds NUMA boundaries, in the case you exceed it, it will lead to an overcommitted host and in the end will cause you performance issues.
  5. A new column modified_extent_page_count, is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count allows you to build smart backup solution, which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup.
  6. SELECT INTO … ON FileGroup - SELECT INTO now supports loading a table into a filegroup other than a default filegroup of the user using the ON keyword support added in SELECT INTO TSQL syntax. This feature in particular will help when you need to run the first step of an archive solution or if you create a "troubleshoot filegroup" so you move the data directly to it instead of you PRIMARY filegroup where you might be troubleshooting a performance issue or anything else and you want to isolate the problem. See SELECT INTO.
  7. A new DMF sys.dm_db_log_info is introduced to expose the VLF information similar to DBCC LOGINFO to monitor, alert, and avert potential transaction log issues caused due to number of VLFs, VLF size or shrink file issues experienced by customers. This dmv helps you to analyze the current health of your transaction log file that can become fragmented due to either an incorrect design or that the current management of your log is not the adequate. Pro Tip: If you have a large number of VLF's that could be one of the reasons why your replications are failing or some other CRUD operations are performing poorly, so go and check this number.
  8. Cluster-less Availability Groups support added. Remember this post (Introduction to SQL Server Always On) where I explained the Key components of Always on, with SQL 2017 Windows Failover Clustering (WSFC) is no longer needed so you can enable the Always On feature even if the SQL Server is not part of a Windows cluster.
  9. Linux. Did I mention SQL 2017 can run in Linux? Well, this new version is fully supported either in Windows or Linux

The items described above are related with the most frequently used with the database engine but there are more, also in this post I'm not reviewing features such as SSIS, SSAS, SSRS or Machine Learning and in this last one Microsoft made a lot of improvements with it.

There are templates already ready in Azure for you to go an create VM's with it or if you like to create containers, there is one in Docker already with Linux (Ubuntu 16.04)and SQL 2017 (https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker)

For a complete list of the new features you can see this sites:



SQL Server 2008 end of support