Managing SQL Server instances in Cloud SQL
SQL Server is now available in a public beta on Cloud SQL, making it easy to move your SQL Server workloads to Google Cloud. Cloud SQL is a fully managed database service that handles automated backups, security patches, high availability, and updates, offering a lower-cost and lower-complexity alternative to on-premises hosting.
There are many database tools that you can use with SQL Server instances in Cloud SQL. For example, the Quickstart for Cloud SQL for SQL Server takes you through how to create an instance and connect to it from SQL Server Management Studio (SSMS), a Windows application for managing SQL Server databases.
In this post, we’ll look at how to connect four popular database tools to your SQL Server instances in Cloud SQL. No matter which tools you prefer to use for running T-SQL queries and performing common database administration tasks, you can use them with Cloud SQL.
Cloud SQL Proxy
The Cloud SQL Proxy is the recommended way to connect to Cloud SQL instances, because it provides improved security and authentication management. The proxy’s TLS encrypted connection is more secure than built-in SSL, and it uses IAM authentication with options for supplying credentials to support a variety of development and deployment scenarios. The proxy works great with client applications such as locally installed database admin tools, and the Quickstart for using the proxy for local testing provides an overview of how to install and configure it for use with SQL Server instances.
The proxy runs on your local machine, managing traffic to and from Cloud SQL via a secure encrypted connection. Applications such as database tools can connect to the proxy locally at 127.0.0.1.
The Cloud SQL proxy needs credentials to authenticate connections to Cloud SQL, and there are several options as covered in the Cloud SQL Proxy documentation. The simplest way to authenticate when using local database tools is to use credentials from an authenticated Cloud SDK client, so we’ll use that approach. Install the Cloud SDK and run the gcloud auth login command, and you’ll be ready to connect with the proxy without any need to authorize IP addresses or configure SSL connections.
When you launch the proxy, use the -instances argument to specify the instance connection name for your Cloud SQL instance. To find your instance’s connection name, click on the instance in the Cloud SQL instance list and then look under the Overview tab. When you use that connection name on the proxy command line you’ll need to add port 1433 (the default port for SQL Server connections), so the proxy command line will look like this:
cloud_sql_proxy.exe -instances=<connection-name>=tcp:1433You can also use the proxy to connect to multiple Cloud SQL instances. In that scenario, you need to assign a different TCP port to each instance. For example, here’s the syntax for connecting to two Cloud SQL instances, with the first one on port 1433 and the second one on port 1434:
cloud_sql_proxy.exe -instances=<connection-name1>=tcp:1433,<connection-name2>=tcp:1434Most SQL Server-oriented database tools default to port 1433, so you would need to explicitly specify port 1434 to connect a local client to the second instance in that example.
When you invoke the proxy from the command line, you may find it useful to make it run in a separate terminal sessions so that you can continue working in the current session. On Linux or Mac OS, you can simply add a & to the end of the Bash command to launch the proxy in a separate subshell. In Windows PowerShell, use this syntax to launch the proxy in a separate process:
Start-Process -filepath “cloud_sql_proxy.exe” -ArgumentList “-instances=<connection-name>=tcp:1433”After the proxy is running and you see the “Ready for new connections” message, you’re ready to connect to Cloud SQL with local database tools, or from your own code running locally.
Azure Data Studio
The SQL Server quickstart covers how to connect with SSMS, which is a popular choice for SQL Server DBAs working on Windows. Azure Data Studio (ADS) is Microsoft’s other tool for managing SQL Server databases, and it’s available for Windows, macOS, and Linux. If you aren’t sure which tool to use, Microsoft has a feature comparison between SSMS and ADS that may be helpful. Note that SSMS is based on Visual Studio and ADS is based on the much lighter weight VS Code editor, so ADS loads more quickly than SSMS and will feel familiar to developers accustomed to working with VS Code.
To try it out, install Azure Data Studio from the download page. Make sure the Cloud SQL Proxy is running (as covered earlier), then within ADS connect to 127.0.0.1 as the Server, and use SQL Login for Authentication type.
After you’re connected, you can write and run T-SQL queries and use all of the other features of ADS. For example, if you want to learn about how T-SQL’s MATCH clause works with graph databases, you can copy the T-SQL script from the documentation page and paste it into ADS and run it. Here’s the output:
mssql extension for VS Code
If you’re working with VS Code, the mssql extension provides code completion, a large library of snippets, and other tools that make it easy to write and run T-SQL scripts.
To connect from VS Code with mssql, follow these steps:
- Install the mssql extension
- Create a script as a
.sqlfile. It doesn’t matter what’s in this script, but a script file needs to have focus in order to connect to a database. - The first time you create a script file after the extension has been installed, a terminal window will open to initialize the SQL tools service. Let this run to completion.
- Go to the Command Palette (Ctrl-Shift-P) and type
sqlcon, then selectMS SQL: Connectand you’ll be prompted to create a connection. Use127.0.0.1for the server name, and select SQL Login for the authentication type and enter your username and password. - After you give your connection a name, it will connect to your database.
Once you’re connected, you can use Ctrl-Shift-E to run scripts while you’re editing them. Script output will be displayed in a panel to the right of the script.
For more information about using the mssql extension, see Use the Visual Studio Code to create and run Transact-SQL Scripts.
mssql-cli utility
The mssql-cli command-line query tool for SQL Server is a cross-platform CLI for running T-SQL commands and scripts against SQL Server instances. See the Installation Guide for information about how to install on Linux, Windows, or Mac OS.
After you’ve installed mssql-cli, use this syntax to connect to the local Cloud SQL Proxy:
mssql-cli -U <username> -S “127.0.0.1,1433”Note that mssql-cli uses a comma to separate the host address and the port, whereas some other tools use a colon.
Getting Started
It’s easy to get started with Cloud SQL, and the beta of SQL Server is now public so anyone can try it out. You’ll find quickstarts, how-to guides, API documentation, and other resources in the Cloud SQL for SQL Server documentation. If you’re new to Google Cloud Platform (GCP), sign up and get a $300 credit that you can use with Cloud SQL and other GCP services.
You can use the Cloud SQL Proxy to connect your favorite database management tools as covered above, so you can manage Cloud SQL instances with the tools you already know. Have questions about working with Cloud SQL? Let us know in the comments below, or see the Support page for other support options. And if you’ll be attending the SQL Server PASS Summit in Seattle, be sure to stop in at the Cloud SQL booth to meet the team and see Cloud SQL in action!
