- [Instructor] Late in 2016 we learned that Microsoft was going to bring SQL server to the Linux platform. I think to a lot of us who have been long time SQL server professionals, this was really shocking and interesting news. Microsoft to their credit has become a much more open source friendly company in recent years, and the fact that SQL server is now on the Linux platform is really cool and it's a big change in terms of engineering and all sorts of internal processes in Microsoft. The most interesting thing about it is that SQL server on Linux is exactly the same code base as SQL server on Windows.
There are some limitations as to what you can do and we'll cover these during this module. But in general you have the same sorts of features available always on availability groups, column store indexes in memory OLTP, all the cool new adaptive query planning features that are part of the SQL in 2017, those are all available on both the Windows and Linux platform. So Microsoft is supporting this starting with SQL server 2017 and they're supporting multiple platforms. So they're supporting Red Hat, Ubuntu, as well as Suse Enterprise Linux.
Microsoft is also supporting docker containers, which you'll learn more about in another module. It's like I mentioned earlier, it's the same code you have on Windows. There are some minor feature limitations, the biggest one I know of right now is transactional replication, and there are a couple such as file stream and file table that would require direct access to the Windows operating system and Windows file structures. So those are probably going to be a long time coming. However the core features like I mentioned, just work. The database engine is exactly the same. Performance is the same across both versions.
You may run into some edge cases where Linux performs a little better for certain tasks, or Windows performs better for certain tasks. But in general and in my extensive testing of both platforms, since the code is the same everything else is the same. Additionally, since the way this is works is that you're loading up Windows run time with inside of your Linux kernel, you have the ability to run CLR or a common language run time code, so C sharp or dot net code inside of your SQL server just like you can on Windows, on Linux, which is kind of cool.
You'll see a later video in which we deploy that. So why did Microsoft decide to do this? In general it appeals to a broader set of customers, so there are a lot of folks that are using my SQL and post gres and have only developed on those platforms, especially younger software developers. Also Microsoft has really embraced open source software. If you look at GitHub and GitHub contributions by organization, you see that Microsoft has a large number of GitHub contributions now, and this is a big deal. There's also the notion of customer demand.
Microsoft at the end of the day, as a publicly traded company that needs to make money, and they wouldn't have done this if there wasn't demand from customers in order to have SQL server running on the Linux platform. Some of the other commercial database platforms are known for having less than friendly licensing terms, and this is to offer an alternative to other database platforms that are on Linux. So let's pop over to a CentOSVM that I have here, and CentOS is just the free version of Red Hat. So in this demo we're going to go ahead and install SQL server on Linux, and there's a little bit of a series of steps you have to make so I have them cut and paste in this text file.
And I'll make this available to you. So here we're going to paste, the first thing we're doing is grabbing the repository and creating a repository from Microsoft. The next thing we're going to do is install SQL server from a package manager. Copy that guy, and this is going to depend on your internet speed. So on my machine on the connection I'm on, this took about three minutes. Okay now that the install is completed, it's important to note that you're getting the latest version of SQL server.
You do have the option of going back versions and getting those, but by default you're going to get the latest version 'cause you can in the repository specify that you wanted an older version. And when I say version I'm just referring to the cumulative update level of the package. So here our install is complete. We have SQL server running, however I'm going to go ahead and stop it because I want to set the SA password and define my addition. And I'm going to use the mssql comp command to do that, but first I need to stop the service. So I'm going to say sudo system control stop myssql dash server, and while that's stopping I'll go ahead and grab the next command.
Now that that's stopped, we're going to go ahead and run that next command. And this is going to have me choose an addition. For your purposes if you're running this at home you should probably choose developer addition, it's free. And then now I'm going to set my SA password so I'll be able to log in. And while that's happening, I'll go get my next couple of commands. The first one's going to be a firewall role that I'm going to configure, and this is so I can add a firewall role to accept transactions from port 14 33, which basically means I can connect management studio and other tools from remote servers.
And I had already enabled that on the server. I'm going to reload my firewall. The next thing I'm going to do is install the tools for SQL server, and this is going to give me BCP and SQL cmd. So here I'm going to go ahead and grab these, and do an install. And this'll go a little bit faster than the engine 'cause they're much smaller. And you have to accept the license terms here twice, and then we'll install. You can automate this and pass in an argument to accept the licensing terms.
I want to do one more thing before I log in. We're going to put the tools in our path, so we're going to export them to our batch profile. USA, I can enter my password, and I see that I have SQL server RTM 2017 CU4 running on Linux on CentOS 7, so pretty simple stuff. It's a little bit different than Windows but in my opinion it's a little bit easier. You'll know there's a couple other components that aren't installed with this, and we'll talk about one of them and that's the SQL agent.
Also you'll need to install full text search and SSIS separately, they're not included on the main install for SQL server.
- Querying with SQL Graph
- String functions in SQL 2017
- Adaptive query processing
- Docker containers and SQL
- Deploying CLR code with SQL Server on Linux
- Working with SQL Operations Studio
- Using snippets
- Code deployment using GIT