SQL Server

Search…
SQL Server
Below you will find details on how to create a tonic user on both the source and destination databases with the minimum level of permissions needed for Tonic to function.  Additionally, this document will cover a few additional steps required for SQL Server to perform optimally and successfully.
Connecting Tonic to your SQL Server Database
Out of the box, Tonic supports SQL Auth login.  This will need to be enabled by your SQL Server Admin in order to use Tonic.  For Windows Authentication, please reach out to [email protected]
Source Database
The following is an example of how to create a new user, called tonic, and grant necessary permissions. For the source database we recommend using a backup of your production database instead of connecting directly to your production environment.  The below statements will create a new Database user called 'tonic' with the SELECT, VIEW DATABASE STATE, and VIEW ANY DEFINITION grants.
1
USE [<Database being masked>];
2
GO
3
CREATE LOGIN tonic
4
    WITH PASSWORD = '<Password Goes Here>';  
5
GO
6
CREATE USER tonic FOR LOGIN tonic;  
7
GO
8
GRANT SELECT TO tonic;
9
GO
10
GRANT VIEW DATABASE STATE TO tonic;
11
USE [master]
12
GO
13
GRANT VIEW ANY DEFINITION TO tonic;
14
GO
Copied!
Destination Database
Initial Setup
Tonic does not create the Destination Database at the time of data generation.  Instead, it requires that the user has already properly setup their database.  Tonic requires that the destination database resemble the source database as much as possible and hence we strongly recommend that you run the CREATE DATABASE and ALTER DATABASE statements exactly as they were written for the source database.  For example, if your source database contains Filegroups they must also be created on the destination database for Tonic to run successfully.
Note: Creating the destination database is a one-time step.  Once created, Tonic will be able to successfully update the contents of the database (including schema-level objects) between generation runs.
Permissions
The destination database requires a higher level of permissions than the source database.  We recommend creating a user with the db_owner role for ease of use.
1
USE [<Database being masked>];
2
GO
3
CREATE LOGIN tonic
4
    WITH PASSWORD = '<Password Goes Here>';  
5
GO
6
CREATE USER tonic FOR LOGIN tonic;  
7
GO
8
EXEC sp_addrolemember 'db_owner', 'tonic'; 
9
GO    
Copied!
Performance Considerations
In order to maximize performance we suggest that you run our destination database with a Simple recovery model.  This will reduce transactional logging and improve the performance of writes to the destination database during generation runs.  The accomplish this in Sql Server Management Studio please right-click on the destination database and select Properties on the context-menu.  From their, navigate to the Options sub-menu and selected 'Simple' from the Recovery Model drop-down.
Database Specific Setup - Previous
PostgreSQL
Next - Database Specific Setup
MySQL / MariaDB
Last modified 1yr ago
Copy link
Contents
Connecting Tonic to your SQL Server Database
Source Database
Destination Database