Have you ever been setting up a new Windows machine and dreaded having to download and install database tools that are several gigabytes in size? Or, on the other hand, wanted to do some work on your SQL Server database from a Mac and wished you had SQL Server Management Studio (SSMS)? Maybe you’ve been using Visual Studio Code (VS Code) and have thought, “Gee, it would be so cool if there could be a version of SSMS like this! Cross-platform, lightweight, extensible and free!” A nerd has her dreams, I guess.
- Sql Server Management Studio For Mac
- Sql Server Management Studio Os X
- Sql Management Studio Express
- Sql Management Studio Reports
SQL Server Running on a Mac?! You can start by creating a backup of your existing database using SQL Server Management Studio running on your Windows PC/Server. Generate a backup file. SQL Server Management Studio currently runs only on Windows machines, so if you need to work with your SQL Server databases on a machine running macOS, you will want to look for a data client that: 1. Integrates and is capable of connecting to you.
Well, friends, dreams do sometimes come true. I’ve already had a lot of fun working with the mssql extension for VS Code, but that extension is mostly for executing SQL, though it also has some great features for visualizing query results. And it made me wish for more, such as, “If only I could browse through my database schema with an explorer like the one in SSMS or SQL Server Data Tools (SSDT) in Visual Studio.” Or, “If only I didn’t have to go look up the TSQL to do a database backup.'
The realization of these dreams comes via the new Microsoft SQL Operations Studio. It seems that with the mssql extension for VS Code, the SQL Server Tools team was just getting warmed up. This team is very focused on having its upcoming tools be not only cross-platform, but also fill the needs of many types of users—DBAs, sys admins, accidental DBAs and developers.
VS Code provided a great starting point for SQL Operations Studio: It’s cross-platform, highly extensible and written in ElectronJS, a platform for building desktop applications in JavaScript, HTML and CSS. In fact, if you’re familiar with VS Code, you’ll recognize the surface of SQL Operations Studio because of that VS Code starting point.
SQL Operations Studio debuted at the PASS Summit in October and a public preview was launched at Connect();. It’s a free, standalone tool that works with Azure SQL Database, Azure SQL Data Warehouse and SQL Server running anywhere. You don’t need a SQL Server license to use it. SQL Operations Studio is available for Linux, macOS and Windows at aka.ms/sqlopsstudio, and takes just moments to install. You’ll find the source code for SQL Operations Studio on GitHub at github.com/microsoft/sqlopsstudio and anyone in the community can file issues or suggestions and contribute to the product on GitHub.
Just because it’s cross-platform, I had to first try it out on my MacBook where I already have a few SQL Server for Linux servers running in Docker, but can also interact with some Azure SQL Databases or even connect to a SQL Server instance on a Windows Server on my network.
The SQL Operations Studio IDE
Let’s take a look first at what you see when you start up SQL Operations Studio for the first time (see Figure 1). The arrows and text aren’t part of the IDE, just there to help with the tour.
Figure 1 The SQL Operations Studio UI
The action bar on the left, familiar to VS Code users, has 5 icons: Object Explorer, like its counterparts in SSMS and other database IDEs, lets you view and manage objects; Task History shows you the tasks, such as backup and restore, that have been performed; File Explorer provides a way to store TSQL and other assets you want to associate with a particular project; Search here focuses on the files you’re working with, but don’t worry, there’s also a way to search the database. Finally, Source Control leads to tools for the integrated source control of the project files.
Make Your First Server Connection
Like SSMS, SQL Operations Studio lets you connect to multiple servers and remembers the connections so you can easily reconnect whenever you open the application. I’ll start by connecting to a SQL Server for Linux instance in a Docker container on my machine. First, I need to be sure the container is running, so I’ll open the integrated terminal window with CTRL+~ and type the Docker command, docker ps. The response tells me the juliesqllinux container is running and I know it’s available at localhost.
Next, I’ll click on the Object Explorer icon to open the Object Explorer, and then I’ll click the Add Server icon at the top right.
This opens up a connection window that I’ll populate with my connection information. As with SSMS, you can just add the basic connection information and let the defaults do the rest of the job. Or you can click Advanced and specify more information about the connection. I’ll go with the easy option. After filling out the connection information, click Connect.
The server will appear in the Object Explorer and you’ll be presented with some information about it in the dashboard. In Figure 2, I’ve partially expanded the server and one of the databases so you can see how much detail is available in Object Explorer. Knowing what was already possible, thanks to my experience with the mssql extension for VS Code (SQL Operations Studio includes all of those features), the addition of this explorer feature sold me instantly on SQL Operations Studio. But, of course, there’s more.
Figure 2 The Object Explorer and Servers Dashboard
Dashboards Provide Easy Access to Stats and Management Tasks
Servers and databases both have dashboards and you can customize what’s on them (more on that shortly). If you right-click a database within the server database and choose Manage, you’ll see that it also has a Backup Task button.
Returning to the icons at the top of Object Explorer, the middle one is for creating server groups. I’ll click on that and use the simple form to create a group named DockerServers and assign it a color. Then I’ll create a second one called AzureServers.
Both of these groups will now show in the explorer window. I can drag the existing localhost onto the DockerServers Group bar and it will move it into that group, as shown in Figure 3.
Figure 3 Server Groups in the Servers Explorer
Next, I’ll create a connection to one of my Azure SQL servers. There are a few paths, but because I want it in the AzureServers group, I can click on the green bar and choose New Connection from its context menu. Back in the connection window, you’ll see that the last option provided is Server group. That will be auto-populated with AzureServers and then I can fill out the rest of the information to connect to my Azure SQL Database server. After I’ve made the connection, I can see that server, its databases and the dashboard, which tells me it’s an Azure SQL Database (see Figure 4). Notice that the Backup and Restore tasks aren’t available. Azure SQL Database backs up data automatically, so there’s no need to have an explicit task for triggering such operations. Restore works differently in Azure SQL Database, so I’m hoping to see a Restore task for Azure SQL Database in a future update.
Figure 4 The Servers Dashboard for an Azure SQL Database Server—SQL Operations Studio Doesn’t Show the Backup Task for Azure SQL Database Because Backups Are Automatic
Interacting with Data
Let’s work with some data! I added the tried-and-true AdventureWorksLT database into my Azure SQL Database server because it’s available as a sample when you create a new server. Another bonus is that it’s prepopulated with lots of data. I’ll do something I often do in SSMS—manually edit some data from a table. Expanding AdventureWorksLTSample again, I’ll right-click on the SalesLT.Customer table in the explorer, which displays a context menu filled with functions, as shown in Figure 5.
Figure 5 The Context Menu for a Database Table
I’ll choose Edit Data to open up the grid shown in Figure 6, which has a Max Rows dropdown defaulted to 200 (a safe bet when pulling data over the Internet. The other options are 1,000 and 10,000.) I edited the Title in the first row, which was saved automatically when I moved to another cell. To see if the table really updated to Azure, I then opened a new query window (CMD+N) and was happy to have IntelliSense help me type SELECT * from SalesLT.Customer, prompting me for the available schemas and objects. I was also pleased to discover I could use the familiar F5 keystroke to execute queries—something you can’t do in VS Code because so many tools and extensions have to share the keyboard shortcuts.
Figure 6 Editing Data in SQL Operations Studio
While you can edit data directly, you can’t yet edit the database schema visually. The Script as Create menu item is the closest you can get in these early days of SQL Operations Studio to being able to modify schema in the database.
Customizing the Dashboard
Most of the features of the mssql extension for VS Code are in SQL Operations Studio. If you’ve used that extension already, or read my article (msdn.com/magazine/mt809115) or watched my Pluralsight course (bit.ly/PS_MSSQL), you may already be familiar with the many features of the SQL editor, as well as the available snippets for writing and executing queries and commands against a SQL Server database. The query results window, with its ability to export results to CSV, JSON or Excel files, is another feature that came from the extension. In SQL Operations Studio, a new addition to the query results window allows you to tap into some of the amazing extensibility in this IDE.
Let me demonstrate and then do a big reveal of something I’ve never done in SSMS as I complete this little demo. In preparation for this functionality, it’s time to work with the file system. Create a folder on your computer where you’ll save some of the SQL you’ll be writing.
Back in SQL Operations Studio, click the File Explorer icon in the Activity Bar and then the Open Folder button to open the folder you created. The File Explorer will display the folder and any files within (currently there are none).
You can create a new file inside the folder by clicking the “new file” icon to the right of the folder name in the File Explorer window. Be sure to give it a .sql extension. As shown in Figure 7, I’ve called mine TableSizes.sql.
Figure 7 Snippets to Help with Tricky SQL Commands
I’m interested in some metadata about my AdventureWorksLT database: how many rows are in each of the tables and how much space are they taking up on my drive? Or, in this case, how much storage in my Azure account? Rather than spending hours trying to figure out how to write that query, thankfully there’s a snippet that gives me just what I need, plus a bit more metadata along the same lines.
In the editor window, start by typing SQL and you’ll see a list of the snippets. SQLGetSpaceUsed, shown in Figure 7, is the one I’m looking for. You can tab to auto-complete the snippet name and then hit enter to display the snippet’s SQL in the editor window. The snippet has a placeholder to type in a table name, but I don’t want to filter on a particular table; I want all of the tables. Scroll down to line 20 of the SQL and remove the following line:
The query returns the following columns:
Sql Server Management Studio For Mac
I only want two pieces of data, the rowcount, now named Rows, and a twist on the data_space_MB. I’ve deleted all the other lines above and modified the two I want to keep:
Notice that I changed the total space from megabytes to bytes by multiplying by 1.024 rather than 1,024. I have my reasons and you may agree when you see the results. Be sure to save the file. I’ve modified the VS Code files.AutoSave setting to save “AfterDelay” so I don’t have to remember.
Now run the query and you’ll be prompted to choose the connection on which to run it. If you don’t specify a database, the query will be run against the master database. A handy recent connections list should make it easy to set the connection without having to fill the form out manually again. I could have created the new query window from the server, database or table and avoided having to select the connection for the query to run on, but then I would have had to specify where to save the .sql file. But because I happened to start with the file, I have to explicitly choose the connection. After selecting the connection, you’ll see a grid of the query results displayed below the editor window.
To the right of the results, there are four icons. The first exports the results to a CSV file, the second to JSON, the third to Excel—all functionality that came over from the mssql extension. The fourth icon is new to SQL Operations Studio. It will create a graph from the data, with a variety of graph types to from which to choose. I was surprised by this feature—it’s one that’s reminiscent of sophisticated business intelligence (BI) tools. A horizontal graph suited my needs. In Figure 8, you can see the row count in pink and the bytes in blue. The reason I calculated bytes, not megabytes, is because the megabyte values would have been too small to appear on the chart.
Figure 8 A Graph Generated from Query Results
That’s already pretty cool, but wait, there’s more! Copy as image and Save as image are great features, but they pale in comparison to the third option. Click the Create Insight button above the graph to open a new window with JSON that describes the graph in the form of a widget (see Figure 9). Widgets are a powerful feature of SQL Operations Studio that can help you create a lot of visual customizations.
Notice the queryfile value pointing to the .sql file I saved in my project folder. The widget knows to execute that query when it comes into view. If you make changes to the query in the file, those changes will be reflected in the widget the next time it’s run. I want to tweak two things that I didn’t modify in the Chart Viewer, which I can do directly in the JSON. I’ll change the value of legendPosition to “top” and columnsAsLabels to “true.”
The next task is to get this widget into the SQL Operation Studio settings. Copy the full text of the JSON file, then open the settings window again (CMD+,). On the left pane where the default settings are listed, use the search box to find “dashboard” and look within the results for dashboard.database.widgets. Hover over that text and a pencil icon will appear to the left. Click the pencil icon and select the “Replace in settings” menu option that appears. This will cause the entire section of the default setting to be copied over to the user settings panel on the right. VS Code is so cool, isn’t it? Now, paste the JSON you copied above the opening brace for the Tasks widget, as shown here, and follow it with a comma:
Save the settings file and close it. Then, back in Object Explorer, right-click on one of the databases and choose Manage to see its dashboard. The new widget appears on the dashboard (see Figure 10) and on the dashboard of every database you open. The query it’s tied to will run on-demand when you open the dashboard for a current view. This widget won’t appear on the Servers dashboard because I specifically placed it in the Database dashboard settings, where it makes the most sense. But imagine the types of metadata you can expose visually on the dashboards to, for example, see which queries are running slowly or perform other health checks or view important statistics. You can also use the settings to control how widgets are laid out in the dashboard.
Figure 10 The Dashboard for the AdventureWorksLTSample Database with the New Widget in Place
We’ve Just Scratched the Surface
There is so much more to discover and do in SQL Operations Studio. Here are a few extra tidbits before wrapping up.
You may have noticed the Explain button on the query window. It will show you query plans just as you see them in SSMS, with an alternate grid view, as well.
Sql Server Management Studio Os X
The file you created for the TableSize query can now be tracked and shared with the integrated source control. I’ve already been doing that in this project to save some queries that I spent too much time working out. If your team is using source control already for your databases, you’ll find many more sophisticated uses for this feature.
Take a look back at the dashboard in Figure 1 and notice the search widget. A Server dashboard will show a list of its databases and you can easily search for database objects rather than perusing through the Object Explorer. The Database dashboard will show a list of tables, views, functions and procedures, and you can search for database objects by name there, as well.
You can learn so much more about SQL Operations Studio in the official docs at aka.ms/sqlopsstudio, where you’ll find detailed documents about its features, as well as walk-throughs. Remember that SQL Operations Studio was spawned from VS Code, which already has more than 4,500 extensions, most of which have come from the community. In addition to the enormous amount of work that the SQL Data Tools team is pouring into SQL Operations Studio, this new tool will likely take on a life of its own when its own ecosystem of extensions begins to evolve.
Head over to aka.ms/sqlopsstudio to download SQL Operations Studio for Linux, macOS and Windows and check out the Getting Started guides. You can watch videos of SQL Operations Studio at aka.ms/sqlopsstudio-tutorial. And don’t forget to provide feedback, file issues, make suggestions and submit pull requests to improve SQL Operations Studio at github.com/microsoft/sqlopsstudio.
Julie Lermanis a Microsoft Regional Director, Microsoft MVP, software team mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at the thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at juliel.me/PS-Videos.
Thanks to the following Microsoft technical experts for reviewing this article: Eric Kang and Sanjay Nagamangalam
Just last year I got my first Macbook Pro for development, after a lifetime of Windows (and linux/unix on server environments). I was very excited to get my environment set up as many friends who work in a similar field had sworn by it. I knew that almost every element of my workspace would be easily transferrable to OSX, and in some cases more elegantly. Technologies such as Apache, PHP, ssh/shell, etc are almost more native to the *nix core than they are to windows.
There was however a single piece of the puzzle that I knew I still had to deal with. Microsoft SQL Server. My work use it, personally I woudln’t choose it for my own projects at all, I’d choose something less platform dependent. Having said that I actually don’t mind the product OTHER than it’s requirement to be run on Windows. It is a decent product, it’s professionally supported, and most of the clients in my industry use it. In fact not so long ago some may argue that it was one of the best choices for large systems. It was FAR superior to the well known MySQL and a lot more affordable than Oracle. Let’s not forget it was originally bought from Sybase (which is probably why the product isn’t all that bad! Microsoft didn’t create it!)
Sql Management Studio Express
So basically I needed to use MS SQL Server. I have a seperate windows box with an SQL Server instance on it, and what I needed was a client program that could connect to the server so I could query it, write SQL, interrogate data etc. I needed the equivalent of Microsofts SQL Management Studio (which used to be called “Query Analyzer”), or “MySQL Workbench“.
Sql Management Studio Reports
One option of course was to install VMWare, install Windows, install Microsoft SQL Server and just use Microsofts native client. But I really didn’t want to do that. So after much reading & researching I narrowed my options down to a few potential candidates:
So I tried all these products, and here’s the problem. They’re ugly. They’re clunky, awkward and … ugly. Most likely because they’re Java based – but they’re just hideous. When you get used to an environment like MS SQL Server, you just can’t revert back to this kind of rubbish. It’s funny you know, when you research this stuff, you see a lot of people recommending these products. And after using them you immediately realise that these people either a) come from an old school linux or java background where everything is ugly and they’re used to it, b) they’ve never worked with large or complicated datasets where EFFICIENCY is of utmost importance and probably consider phpMyAdmin suitable for data wrangling.
But the problem is they’re not ok. I’m sorry, they’re just not. They’re unpolished. There are real annoyances – the forms are hideous. The result set grids will have 10px padding so you can only see about 5 rows of result sets, or the result set grids cells don’t automatically resize to the width of the content within the cell (within reason) – they just don’t cut it. It’s situations like this when you realise the word ‘enterprise’ actually does have a meaning beyond just being a buzzword!
I was about to lose hope then all of a sudden, another contender crossed my path:
It was beautiful – I was in love. It’s a completely professional, sexy, clean, efficient, enterprise grade, high end. powerful SQL interface. It’s actually quite close to Microsoft’s native client in feel. I got it all hooked up, everything was working well, I even connected over mmy SSH tunnels to remote clients (an article on that later). It was all looking good until I ran into a problem. Running some stored procedures gave an error.
It turns out that SQL Developer is fine with standard SQL like SELECT, INSERT, UPDATE, DELETE etc, but when running certain SQL Server specific commands, SQL Developer doesn’t know how to interpret it, or interprets it correctly. A common problem seems to be the BEGIN and END keywords. It also can’t handle multiple resultsets returned from a stored procedure, and won’t handle the “go” command in SQL Server scripts.
So unfortunately in the end I installed Windows XP (for it’s light weight), MS SQL Server and just use the native Microsoft SQL Management Studio. Given that my brand new Macbook Pro handles this without struggling it’s actually a fairly reasonable solution, but I cross my fingers that one day Oracle SQL Developer is completely compatible, or that someone makes a worthwhile GUI.
It’s worth mentioning that if you do fairly straight forward queries without very large datasets or without complex SQL, some of the above products may well be suitable for you, I’m happy to answer any questions you may have in the comments.
Edit (6th Jan 2015): It looks like some developers by the name of Hankinsoft ran into the same issue as many of us and have actually gone ahead and developed an MS SQL Client for Mac to address this very issue. The product is called SQLPro for MSSQL and although I’ve only recently started using it, I have to say so far it’s very impressive. It’s polished and pleasing-to-the-eye with it’s native Mac App design & feel, but at the same time is fast, responsive, efficient and dedicated to the task. It’s described as ‘lightweight’ and that’s what I love about it – so far, it’s handled everything I’ve thrown at it (including cross database joins etc), but isn’t overloaded with unnecessary features/preferences/bloating. So far I definitely recommend it.