Strategy: Superior Consulting Services (SCS) provided a database administrator and infrastructure consultant to implement the project. The two resources worked alongside our client’s team to develop an analytical database environment for reporting and dashboards. SQL Server Replication was used to keep the data in sync between the two environments. SCS built a high availability solution, enabling the environments to failover automatically should there be a failure at the client’s primary location. The client needed a test reporting environment, with the same reports as production, but used a test database as the data source. SCS built a routine to migrate the reports from production to test on a nightly basis and change the data connections to the test database after the migration was complete.
Testing: The solution was tested in many different ways. The data in the analytical environment was compared to the transactional environment on a nightly basis to ensure all transactions had been replicated. The high availability solution failed over several times to ensure that the failover process worked successfully and the data was available after failover. Failover was planned to take place when the SQL Server is patched quarterly.
What work did SCS perform?
-
SCS installed SQL Server 2019 Enterprise Edition on four database instances and configured the servers utilizing best practices. The existing transactional environment was then migrated to one of the four database instances.
-
SCS installed and configured a Power BI server in the client’s reporting environment and migrated existing SQL reports to the new reporting environment.
-
SCS configured SQL Server Availability Groups in both the transactional and analytical environments to provide failover capability between the two pair of servers.
-
Replication was then built and configured to send transactions to the analytical environment from the transactional environment.
-
All solutions were clearly documented and provided to the client.
