TRELLIS Core Database

The TRELLIS Core Database can be deployed on a single database server for most SME solutions. The core database will run with Instance tables containing over 100 million records with good performance characteristics on relatively small database servers.

This means that for most SME deployments, a single HA database cluster is an appropriate solution. You can use either SQL Server Failover Clustering or Always On Availability Groups depending upon the level of HA required and the Disaster Recovery Plan requirements.

Azure Encrypted Database Backup

GreatIdeaz recommends a daily full backup for all databases and a transaction log backup at least every 4 hours - preferably, every hour. The specific database backup schedule should reflect the needs of the solution’s Disaster Recovery Plan and, where possible, ensure that the Recovery Point Objective (RPO) can be achieved from the offsite database backup alone. For very short RPO timeframes, it may be necessary to supplement the scheduled backup strategy with a live DR site maintained using Always On Availability Groups.

Database backups should be encrypted using certificates, and shipped to cloud storage.

On the server to be backed up, create Credential (access Azure) and Certificate to encrypt:

    USE MASTER;

    create credential greatideazcanada with identity = 'greatideazcanada', secret = 'cSKrky5Evdr/4WvMgku+LtYWTQ==';

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'J*HGT***7';

    CREATE CERTIFICATE GreatIdeazCert WITH SUBJECT = 'GreatIdeazCert';

    BACKUP CERTIFICATE GreatIdeazCert TO FILE = 'D:\Temp\GreatIdeazCert.dat' WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = 'J*HGT***7', FILE = 'D:\Temp\GreatIdeazCertKey.dat')
On the restore server create the Certificate to decrypt:

    USE MASTER;

    create credential greatideazcanada with identity = 'greatideazcanada', secret = 'cb0q7XKrkdqFf7RHe6vlOujzrWelmwy5Evdr/4WvMgku+bgUWLtYWTQ==';

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'J*HGT***7';

    CREATE CERTIFICATE GreatIdeazCert FROM FILE = 'd:\temp\GreatIdeazCert.dat'
    WITH PRIVATE KEY
    (
    FILE = 'd:\temp\GreatIdeazCertKey.dat',
    DECRYPTION BY PASSWORD = 'J*HGT***7'
    )
To backup a database:

    DECLARE @URL AS VARCHAR(200) = 'https://grdb.blob.core.windows.net/dev/TRELLIS-Core/' + '20161223' + '.bak'
    BACKUP DATABASE [TRELLIS-Core]
    TO URL = @URL
        WITH CREDENTIAL = 'greatideazcanada'
        ,FORMAT, COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = GreatIdeazCert)
        ,STATS = 5;

Scaling Strategy

The TRELLIS Core Database model is deigned to be both vertically and horizontally scalable. The initial response to scaling should be to examine options for increasing the size of the database server hosting the database. Options to consider are:

GreatIdeaz recommends that the database and log files are always stored on different disks.

An interesting topic to study for MS SQL Server deployments is database files and filegroups. Essentially, we can partition the database files across multiple disks if we are having issues with overall disk size and disk IO Operations per Second. This give us a great way to partition the Instance tables across multiple devices if we start hitting performance issues due to the number of rows.

For extremely large databases we can take this idea one step further – we can scale the Workspace itself across multiple database servers. The core Instance tables can be logically partitioned so that data relating to one set of concepts can be sent to one database, whilst another set could be sent to a second database. This could even be extended to the partitioning of Instance data based on a combination of Concept GUID and Instance GUID. To implement this approach, we would examine the incoming request to the Data at Rest service to examine the Concept GUID and Instance GUID, and then connect to the appropriate database to service the request. For general lists, searches and reads, the data can be aggregated by the Data at Rest service from multiple databases.