This is a prototype of a simple ASP.NET Core 2.0 REST API web app that uses SQL Server Management Objects (SMO) under the covers to provide a RESTful interface for SQL Server running anywhere.
You can run this REST API web app on Linux, macOS, Windows or Docker and optionally use environment variables to connect to a local or remote SQL Server instance, Azure SQL Database and Azure SQL Data Warehouse.
For fun, I've added GET REST API end-points to:
- navigate Server -> Databases -> Tables -> Columns
- generate
CREATE DATABASEandCREATE TABLET-SQL scripts - view data in tables
Thanks to SMO, the REST API always returns up-to-date information when any schema or data changes occur in the database while the web app is running.
Currently, only the the GET verb is supported. In the future, I plan to add support for:
- other REST verbs (
PUT,POST,UPDATEandDELETE) - more database objects such as Views, Stored Procedures, Schemas and UDTs
- pagination for tables with a large number of rows
The instructions below are for a MacBook. Modify as needed if you're using Linux or Windows.
- Download and install Docker for your operating system: https://www.docker.com
- Increase Docker memory to 4 GB to run SQL Server 2017 as documented here
Copy & paste the commands below in a Terminal window to run SQL Server 2017 in Docker.
docker pull microsoft/mssql-server-linux:2017-GA
docker run --cap-add SYS_PTRACE \
-e 'ACCEPT_EULA=Y' \
-e 'MSSQL_SA_PASSWORD=Yukon900' \
-e 'MSSQL_PID=Developer' \
-p 1433:1433 \
-d microsoft/mssql-server-linux:2017-GA
This REST API web app is available on Docker hub: https://hub.docker.com/r/sanagama/mssql-restapi
Copy & paste the commands below in a Terminal window to run the REST API web app in Docker and connect to SQL Server 2017 running locally in Docker.
docker pull sanagama/mssql-restapi
docker run -it -p 5000:5000 -e MSSQL_HOST=`hostname -s` sanagama/mssql-restapi
NOTE: With the above command, the REST API web app uses default values for port, database, username and password as described in environment variables
TIP: Google Chrome with the JSON Formatter extension is a great way to play with REST APIs.
- Launch your browser and navigate to http://localhost:5000/api/mssql
- Click on the various links in the JSON response to navigate databases, tables, columns and table data in the SQL instance and generate scripts.
That's it, all done!
Keep reading if you want to get the source code and walk through a more advanced example with the AdventureWorks and WideWorldImporters SQL Server sample databases.
Download and install .NET Core for your operating system: https://www.microsoft.com/net/core
TIP: If you have
Gitinstalled then you can dogit clone https://github.com/sanagama/mssql-restapi.gitinstead.
- Browse to https://github.com/sanagama/mssql-restapi
- Click
Clone or Downloadthen clickDownload ZIP - Save the ZIP file to your
HOMEdirectory as~/mssql-restapi.zip - Extract the zip file to your
HOMEdirectory~/mssql-restapi
Launch a Terminal window and type the following commands to run SQL Server 2017 in Docker:
cd ~/mssql-restapi
cat ./scripts/1-docker-pull-mssql.sh
./scripts/1-docker-pull-mssql.sh
Type the following commands in the Terminal window to restore a couple of sample databases to SQL Server 2017 running in Docker:
cd ~/mssql-restapi
cat ./scripts/3-docker-create-db.sh
./scripts/3-docker-create-db.sh
Type the following commands in the Terminal window to run the REST API web app locally:
cd ~/mssql-restapi
dotnet restore
dotnet build
dotnet run
- Launch your browser and navigate to http://localhost:5000/api/mssql
- Click on the various links in the JSON response to navigate databases, tables, columns and table data in the SQL instance and generate scripts.
You can pass environment variables to the REST API web app to connect to a local or remote SQL Server instance, Azure SQL Database and Azure SQL Data Warehouse.
| Environment variable | Description |
|---|---|
| MSSQL_HOST | Fully qualified server name. Defaults to 127.0.0.1 if not specified. |
| MSSQL_PORT | SQL Server port. Defaults to 1433 if not specified. |
| MSSQL_DATABASE | Initial catalog for the connection. Defaults to master if not specified. |
| MSSQL_USERNAME | Username for SQL Server authentication. Defaults to sa if not specified. |
| MSSQL_PASSWORD | Password for SQL Server authentication. Defaults to Yukon900 if not specified. |
You can connect to Azure SQL Database or Azure SQL Data Warehouse by passing your connection information in environment variables when starting the REST API web app.
TIP: Follow instructions at Configure a server-level firewall rule to allow the computer running the REST API web app to connect to your Azure SQL Database or Azure SQL Data Warehouse.
TIP: Change <server>, <username> and <password> in the example below as appropriate to connect to your Azure SQL Database or Azure SQL Data Warehouse.
Type the following commands in the Terminal window to run the REST API web app in Docker:
docker run -it -p 5000:5000 \
-e MSSQL_HOST="<server>.database.windows.net" \
-e MSSQL_PORT="1433" \
-e MSSQL_USERNAME="<username>" \
-e MSSQL_PASSWORD="<password>" \
sanagama/mssql-restapi
Type the following commands in the Terminal window to run the REST API web app locally with .NET Core:
MSSQL_HOST="<server>.database.windows.net" \
MSSQL_PORT="1433" \
MSSQL_USERNAME="<username>" \
MSSQL_PASSWORD="<password>" \
dotnet run
My main motivation for creating this prototype was to try out the SQL Server Management Objects (SMO) APIs that recently became available on .NET Core 2.0.
See this Tweet from @sqltoolsguy for the announcement.
Developers and system administrators can finally use the nifty SMO APIs in .NET Core 2.0 client apps (like this prototype) or PowerShell cmdlets on Linux, macOS and Windows to programmatically manage SQL Server running anywhere, Azure SQL Database and Azure SQL Data Warehouse.
Take a look at the SQL Server Management Objects (SMO) Programming Guide for samples and API reference documentation.
Happy coding with SMO APIs and SQL Server running everywhere ;-)