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.
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]
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.
USE [<Database being masked>];GOCREATE LOGIN tonicWITH PASSWORD = '<Password Goes Here>';GOCREATE USER tonic FOR LOGIN tonic;GOGRANT SELECT TO tonic;GOGRANT VIEW DATABASE STATE TO tonic;USE [master]GOGRANT VIEW ANY DEFINITION TO tonic;GO
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.
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.
USE [<Database being masked>];GOCREATE LOGIN tonicWITH PASSWORD = '<Password Goes Here>';GOCREATE USER tonic FOR LOGIN tonic;GOEXEC sp_addrolemember 'db_owner', 'tonic';GO
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.