A Fine Slice Of SQL Server
Andrew Pruski notes that you can delete active databases in SQL Server on Linux:
What we’re going to do is delete the database files whilst the instance is up and running. Something you can’t do to a database running in an instance of SQL on windows as the files are locked.
Click through for the results, which are counter-intuitive for Windows admins, as well as the reason for this behavior.
Comments closedJoey D’Antoni has some notes on clustering a SQL Server instance running on Linux. First, some quick notes:
One other thing that wasn’t in BOL, that I had to troubleshoot my way through is that just like a cluster on Windows, you have a cluster identifier and floating IP address. I had to add that to /etc/hosts on each of my nodes to get it to resolve. The article mentions turning off fencing for non-prod environments—I had to do that in order to get failover working correctly in my environment.
Then some more notes:
It was faster than building a Windows cluster
It took me a while, I laughed, I cried, I cursed a lot, but if I look at the time it took for me to actually build the cluster and install SQL Server, it was a much faster process. Much of this comes down to the efficiency of the SQL Server installation process on Linux, which is as simple as running yum install mssql-server (mostly). Which leads me to my next point..
As Joey notes, SQL Server clustering on Linux is in its infancy. It’s nice that it works right now, but expect improvements over the next version or two.
Comments closedRob Sewell shows how to run SQL Server queries using Visual Studio Code:
Reading this blog post by Shawn Melton Introduction of Visual Studio Code for DBAs reminded me that whilst I use Visual Studio Code (which I shall refer to as Code form here on) for writing PowerShell and Markdown and love how easily it interacts with Githuib I hadn’t tried T-SQL. If you are new to Code (or if you are not) go and read Shawns blog post but here are the steps I took to running T-SQL code using Code
I played around with an early version of this and my thought was that there were some nice improvements over Management Studio (like being able to filter and sort the result set grid without going back to the server), but that there are still too many nice things Management Studio does for me to take a serious look at it. Still, I’m hopeful that Microsoft moves in the direction of having a fully-featured querying tool for Linux so I can finally join the perpetual Year of the Linux Desktop.
Comments closedTo download the latest PowerShell Open Source just go to the link below:
https://github.com/PowerShell/PowerShell
Just remember to remove the previous version, and any existing folders as this will be resolved later.
To download the latest SQL Server vNext just check the following Microsoft blog post as the new CTP 1.1 includes version both Windows and Linux:
SQL Server next version Community Technology Preview 1.1 now available
Max has additional links and resources in that post as well.
Comments closedSlava Oaks is back blogging and gives us some insight on how SQL Server on Linux came to be:
To give you an idea of the effort involved, the SQL Server RDBMS and other services that ship with it in the SQL Server product suite account for more than 40 million lines of C++ code. Even though SQL Server has a resource management layer called SQLOS, the codebase bleeds Win32 semantics throughout. This means a pure port could take years just to get compiling and booting let alone figuring out things like performance and feature parity with SQL Server on Windows. In addition, doing a porting project while other SQL Server innovation is happening in the same codebase would have been a daunting task and keep the team in a close to endless catch-up game.
In conclusion, even though the potential job-offer intrigued me, it felt like an impossible task for one to take on.
It’s a great story, one which I never would have thought possible six years ago.
Comments closedAndrew Peterson shows how to start, stop, and restart the SQL Server service on Linux:
Start Service
sudo systemctl start mssql-server
He also shows how to do a status check. This is for distributions which use systemd, which includes the Red Hat distribution set (Fedora, CentOS, Red Hat Enterprise). If you’re on Ubuntu, there’s no support quite yet, but you can use start and stop.
Comments closedMax Trinidad shows that Powershell remoting works for connecting Linux to Windows and vice versa:
Connecting Linux to Windows
This is strange but, more likely, you will get an error the first time you try to connect. Just try the second try and it will work.
I need to see what Powershell objects for Linux currently exist; my guess is “not many, if any” but as those start getting fleshed out, I think even the most adamant of grep-sed-awk users will want to pick up at least a little bit of Powershell.
Comments closedSanjay Nagamangalam looks at different tools you can use to connect to SQL Server:
New SQL command line tools for Linux: We’ve created Linux-native versions of your favorite SQL command line tools such as sqlcmdand bcp and sqlpackage and also added the new mssql-conf tool that lets you configure various properties for the SQL Server instance on Linux (e.g., SA password, TCP port and collation).
New versions of SSMS, SSDT and SQL PowerShell: We have released updated versions (v17.0 RC1) of our flagship SQL Server tools including SQL Server Management Studio (SSMS), Visual Studio SQL Server Data Tools (SSDT) and SQL PowerShell with support for the SQL Server v.Next on Windows and Linux.
They also have a plugin for Visual Studio Code, which can be helpful if you’re running on Linux.
Comments closedBut my Backup file is still not visible in the wizard!
Permissions. If you drill down into the folders in Linux, we found that the files already present in the /data/ folder are owned by the user mssql. Our recently copied backup file is NOT owned by mssql, and it not accessible to other users. So, our wizard cannot see the file.
The whole process is pretty straightforward.
Comments closedAndrew Peterson runs chmod 664 on a database backup:
You’re attempting to RESTORE a SQL Server database backup to your Linux installation and you get the message:
Msg 3201, Level 16, State 2, Line 17Cannot open backup device ‘C:\home\user\Downloads\AdventureWorks2012.bak’.
Operating system error 2(The system cannot find the file
specified.).Msg 3013, Level 16, State 1, Line 17RESTORE HEADERONLY is terminating abnormally.
If you’re going to administer SQL Server on Linux, it’s a good idea to check out the Unix-style permissions model. It’s a bit different than what we’re used to on Windows, though it does make sense with a bit of practice.
Comments closed