2019
2017
2016
2014
2012
URL format | jdbc:sqlserver://host:port;databaseName=database |
---|---|
SSL support | Yes - add ;encrypt=true |
Maven Central coordinates | com.microsoft.sqlserver:mssql-jdbc:9.2.1.jre8 |
Supported versions | 4.0 and later |
Default Java class | com.microsoft.sqlserver.jdbc.SQLServerDriver |
/* Single line comment */
CREATE TABLE Customers (
CustomerId smallint identity(1,1),
Name nvarchar(255),
Priority tinyint
)
CREATE TABLE Sales (
TransactionId smallint identity(1,1),
CustomerId smallint,
[Net Amount] int,
Completed bit
)
GO
/*
Multi-line
comment
*/
-- TSQL
CREATE TRIGGER dbo.Update_Customer_Priority
ON dbo.Sales
AFTER INSERT, UPDATE, DELETE
AS
WITH CTE AS (
select CustomerId from inserted
union
select CustomerId from deleted
)
UPDATE Customers
SET
Priority =
case
when t.Total < 10000 then 3
when t.Total between 10000 and 50000 then 2
when t.Total > 50000 then 1
when t.Total IS NULL then NULL
end
FROM Customers c
INNER JOIN CTE ON CTE.CustomerId = c.CustomerId
LEFT JOIN (
select
Sales.CustomerId,
SUM([Net Amount]) Total
from Sales
inner join CTE on CTE.CustomerId = Sales.CustomerId
where
Completed = 1
group by Sales.CustomerId
) t ON t.CustomerId = c.CustomerId
GO
-- Placeholder
INSERT INTO ${tableName} (Name, Priority) VALUES ('Mr. T', 1);
SQL Server supports several methods of authentication. These include:
SQL Server Authentication works ‘out-of-the-box’ with MigrateDB, whereas the others require extra manual setup.
The instructions provided here are adapted from the Microsoft JDBC Driver for SQL Server documentation . Refer to this when troubleshooting authentication problems.
Note: These instructions may be incomplete. MigrateDB depends on Microsoft’s JDBC drivers, which in turn have many environmental dependencies to enable different authentication types. You may have to perform your own research to get the JDBC driver working for the different authentication types.
This uses a straightforward username and password to authenticate. Provide these with the user
and password
configuration options.
Windows Authentication, also known as Integrated Security
, is enabled by amending your JDBC connection string to set integratedSecurity=true
.
Example: jdbc:sqlserver://<i>host</i>:<i>port</i>;databaseName=<i>database</i>;integratedSecurity=true
.
You must add Microsoft’s MSAL4J library to your classpath. For instance, as a Maven or Gradle dependency.
For command-line users, MSAL4J is already included, so no extra installation is required.
There are several types of Azure Active Directory authentication:
To use the various authentication types, amend your JDBC URL to set the authentication
parameter:
authentication=ActiveDirectoryIntegrated
jdbc:sqlserver://host:port;databaseName=database
;authentication=ActiveDirectoryIntegrated
authentication=ActiveDirectoryMSI
jdbc:sqlserver://host:port;databaseName=database
;authentication=ActiveDirectoryMSI
authentication=ActiveDirectoryPassword
jdbc:sqlserver://host:port;databaseName=database
;authentication=ActiveDirectoryPassword
user
and password
configuration optionsauthentication=ActiveDirectoryInteractive
jdbc:sqlserver://host:port;databaseName=database
;authentication=ActiveDirectoryInteractive
authentication=ActiveDirectoryServicePrincipal
jdbc:sqlserver://host:port;databaseName=database
;authentication=ActiveDirectoryServicePrincipal
The Microsoft documentation has more details about how these work with JDBC URLs .
When connecting to a named instance, the JDBC URL must be of the form:
jdbc:sqlserver://<server_name>;instanceName=<instance_name>;databaseName=<database_name>
For example:
jdbc:sqlserver://test_server;instanceName=test_instance;databaseName=test_database
Note: If a named instance is used along with the <host>:<port>
syntax in the JDBC URL, the driver will connect to
the port over the named instance.
CREATE/ALTER/DROP
statements acting on memory-optimized tables (that is, those created with
WITH (MEMORY_OPTIMIZED = ON)
). You will need to override the executeInTransaction
setting to be false,
either on a per-script basis or globally.migratedb.defaultSchema
property
has no value, unless used for a Placeholder in
your sql scripts. If you decide to use migratedb.defaultSchema
, it also must exist in migratedb.schemas
.migratedb.schemas=custom_schema
, as the first entry will
become the
default schema if migratedb.defaultSchema
itself is not set.ANSI_NULLS
or QUOTED_IDENTIFIER
in a script will cause an error. This is
a driver limitation, and can be solved by using the Microsoft driver instead.