Skip to content

Home

Jump to bottom
Benjin Dubishar edited this page Nov 17, 2023 · 32 revisions

Welcome to the SQL Tools Service wiki! This is a .NET-based application supporting SQL Connection, Query Execution, Intellisense and other actions. It's intended for use together with a front-end app such as the MSSQL extension for Visual Studio Code. This wiki will focus primarily on how to build, test, and commit to this repository.

Setup and run the service

Prerequisites to build the repository

  1. Install version of .NET corresponding to the version in global.json from https://dotnet.microsoft.com/download/dotnet
  • Select the platform for your current OS and install

[Update: if you get error after run dotnet restore, try uninstalling earlier versions of .NET and then installing the version from step 1]

  1. Set up CLI or IDE integration (optional):
  • Option 1: Visual Studio Code support: install VSCode, go to the Extensions tab and type "C#". Install the extension and restart VSCode
  • Option 2: Visual Studio Integration (for Windows dev): On the .NET install page there should be a link for .NET SDK for Windows. Click that and run the installer

Clone the repository

  • Start a command line
  • Create a directory where you want to keep your repositories (e.g. C:\repos)
  • Navigate to that directory and type git clone https://github.com/Microsoft/sqltoolsservice.git

Building

  • Navigate to the <root>/src/Microsoft.SqlTools.ServiceLayer folder
  • Run dotnet restore to restore dependencies
  • Run dotnet build to build the project and its dependencies separately. By default this will create a folder in src/Microsoft.SqlTools.ServiceLayer/bin/Debug that will contain the built binaries.
  • Run dotnet publish to create the project and its full set of dependencies needed for runtime. These will be created in src/Microsoft.SqlTools.ServiceLayer/bin/Debug/net#.#/publish by default.
  • To build the Service Layer, run dotnet build [-r|--runtime] src/Microsoft.SqlTools.ServiceLayer/. E.g. - dotnet build -r osx-x64 src/Microsoft.SqlTools.ServiceLayer/ for building on MacOS.
  • To publish the Service Layer, run dotnet publish [-r|--runtime] src/Microsoft.SqlTools.ServiceLayer/

[Update: try dotnet build --runtime win10-x64 for windows10 if you have error during build]

Note If you are adding/modifying strings in the sr.strings file then you will need to run the full build script, see https://github.com/microsoft/sqltoolsservice/blob/main/BUILD.md#build for more details.

Localization

Any strings that are displayed to a user directly should be localized. This includes things like error messages and status updates, but does not generally include log messages.

To add and use a localized string follow these steps:

  • Add a new key/value pair to the sr.strings file for the project you're working in (e.g. [sr.strings])(https://github.com/microsoft/sqltoolsservice/blob/main/src/Microsoft.SqlTools.ServiceLayer/Localization/sr.strings)
    • ; denotes a comment line
    • A line is in the format <key> = <value>
    • The key can include parameters in the form <key>(param1, param2, etc.), which are then inserted into placeholders in the string denoted by {#} where # is the index of the parameter (starting at 0). This should be used whenever you have non-localized values (such as a server name) to insert into a string.
  • Execute build.cmd --target=SRGen to generate .resx and sr.cs files. The .cs file contains a class which can be used to access these string resources in your code

Note

Running build.sh --target=SRGen on macOS currently requires Mono to be installed, resulting in the output XML differing in formatting. To minimize diffs, we currently recommend running this command on Windows.

Troubleshooting

  • Issue: build.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at [https:/go.microsoft.com/fwlink/?LinkID=135170](https:/go.microsoft.com/fwlink/?LinkID=135170)
    • Fix: Need to change the execution policy of CurrentUser to build the file. Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -scope CurrentUser

Testing

Testing in Visual Studio

  • Open Visual Studio, go to File -> Open -> Project/Solution, and open the <root>/sqltoolsservice.sln solution file
  • This will open the project and should automatically initiate a dotnet restore action for you
  • Open Test -> Windows -> Test Explorer and build the solution to view all available tests
  • You can run one or all tests easily from inside Visual Studio

Command-line testing

  • Build the test code in a similar way to src:
    • Navigate to the <root>/test folder and run dotnet restore. This will restore for all test projects
    • The main unit tests are under <root>/test/Microsoft.SqlTools.ServiceLayer.UnitTests. Run dotnet build on this
  • Run dotnet test in the test project's folder to build and execute these unit tests
    • You should see a === TEST EXECUTION SUMMARY === that includes results of running the ServiceLayer unit tests

Testing in Visual Studio Code

  • Navigate to the /sqltoolsservice folder
  • Run "code ." to launch VS Code
  • On the lower-right of the status bar, you should see "3 Projects" listed (or similar). Click on this and choose project.json/test/Microsoft.SqlTools.ServiceLayer.test from the dropdown so that the main unit test project is selected
  • Open a test file, for example test/LanguageServer/LanguageServiceTests.cs
  • You should see options to run test and debug test appearing for each method. Click on these to execute / debug a specific test. For a full test run the command-line integration is still recommended.

Versioning Scheme

The versioning scheme for this project follows the semver versioning system.

One Time Setup to Run Integration, Performance and TestDriver Tests

Tests run against real servers and databases for many end to end tests. To set this up:

Prerequisites

  • A local SQL Server 2019+
    • A quick way to set up a local instance is to use the SQL docker container. Install Docker and then run docker run -e ACCEPT_EULA=Y -e "SA_PASSWORD=******" -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:2019-latest (entering the password to use) to create a container with a 2019 instance, then use localhost as the server in the connection string
  • An Azure server (for the Azure specific tests).
    • Note that when submitting a PR, these tests will be run so can be skipped locally if this is a problem

If you have VSCode and the MSSQL extension installed

  • Setup your connection strings in VSCode
    • Open VSCode, hit F1 and choose "MS SQL: Connect"
    • Choose to "Create Connection Profile", and add all the settings to connect to your local server
    • Choose "Save Password" when prompted"
    • Verify that your connection succeeded.
    • Repeat this for your Azure SQL DB server connection
  • Edit your connections to identify them for use in test runs
    • Hit F1 and choose "User Settings"
    • Find "mssql.connections" and you'll see your 2 new connection profiles
    • For the local connection, add "serverType":"OnPrem".
    • For the Azure connection, add "serverType":"Azure".

If you want to generate the settings

  • Setup your connection instances xml file
    • Create an xml file using the template test\Microsoft.SqlTools.ServiceLayer.TestEnvConfig\SQLConnectionInstancesTemplate.xml
    • Add your connection information to the xml file. At this moment the tests are only looking for the defaultssql2019 instance as OnPrem and defaultSqlAzureV12 as Azure.
    • Go to the test\Microsoft.SqlTools.ServiceLayer.TestEnvConfig directory in a command window and run the following command to generate the settings: dotnet run [path to the instances xml file]

Once this is done, you can run just the integration tests by going to the test\Microsoft.SqlTools.ServiceLayer.IntegrationTests directory in a command window and running dotnet test

Troubleshooting

BadImageFormatException when running tests from Visual Studio

e.g.

OneTimeSetUp: System.TypeInitializationException : The type initializer for 'Microsoft.SqlTools.ServiceLayer.Test.Common.TestServiceProvider' threw an exception.
  ----> System.Reflection.ReflectionTypeLoadException : Unable to load one or more of the requested types.
Could not load file or assembly 'Microsoft.SqlServer.Migration.SkuRecommendation, Version=0.0.0.0, Culture=neutral, PublicKeyToken=dc5fc6b3805d108a'. An attempt was made to load a program with an incorrect format.
Could not load file or assembly 'Microsoft.SqlServer.Migration.Assessment, Version=0.0.0.0, Culture=neutral, PublicKeyToken=15194179b10d770a'. An attempt was made to load a program with an incorrect format.
  ----> System.BadImageFormatException : Could not load file or assembly 'Microsoft.SqlServer.Migration.SkuRecommendation, Version=0.0.0.0, Culture=neutral, PublicKeyToken=dc5fc6b3805d108a'. An attempt was made to load a program with an incorrect format.
  ----> System.BadImageFormatException : Could not load file or assembly 'Microsoft.SqlServer.Migration.Assessment, Version=0.0.0.0, Culture=neutral, PublicKeyToken=15194179b10d770a'. An attempt was made to load a program with an incorrect format.

This is likely because the test explorer is set to use the x86 architecture for AnyCPU builds (the default configuration). To fix this click the dropdown arrow next to the gear (settings) icon in the Test Explorer, select Processor Architecture for AnyCPU projects and select either Auto or x64, then re-run the tests.

Clone this wiki locally