I'm seeking some architecture advice involving the database engine and Integration Services components of SQL Server 2017. We have an existing SQL Server environment which is due for an upgrade and has historically been used in a less than ideal way. With an upgrade to a new SQL Server environment now on the horizon, as a systems administrator I want to use this opportunity to rethink the existing architecture to provide a more robust flexible environment going forward.
The Current Environment
A SQL Server 2012 Enterprise instance running on a 2-node Windows Failover Cluster. The database engine is clustered and DTS packages are stored in a clustered volume and these packages are executed as jobs via the SQL Server Agent. They do not use the explicit Integration Services component
Staff who administer the data warehouse and create/manage the DTS packages have traditionally insisted on working directly on the clustered server nodes via RDP. This creates access issues with the systems admins who are responsible for the servers themselves. The data staff also require a variety of software tools which has led to persistent disk space issues over time. Additionally, many of these software tools are not cluster-aware and are only installed on one node, effectively leaving us with a crippled cluster right from the start. The result is that any time there is a planned or unplanned failover, there is pressure to get the original primary node back online asap because most jobs are failing because they rely on local software which is not cluster-aware.
A Proposed Solution
Install the database engine alone on its own cluster with no RDP access allowed by any staff. They will receive just the SQL Server clustered instance endpoint URL. Install the Integration Services component on a separate server and propose they begin using the SSISDB catalog to store their packages instead of the file system. My understanding is that there are many advantages to using the SSISDB.
- This only shifts the RDP access issue to another server. Because the data staff collaborate in real-time on packages and rely on all the same software tools, they argue they must have direct access to the server in order to provide a shared identical environment. My belief is that there must be a more modern design approach where they can use their development tools on their local workstations and connect to SSIS using the SSMS, where the jobs who be published and scheduled/executed. Any software that packages rely on (ftp, ssh, perl, python, etc) would be installed and managed by server admins on the SSIS. Since packages would be stored in the SSISDB, there would no longer be a need for the shared storage which has traditionally housed the package files and this should remove the need for any direct RDP access. Is my belief logical or is there still some argument for data developers to have direct server access?
- Now the SSISDB becomes a single point of failure since the packages are no longer part of the failover cluster as they traditionally have been. How would I build high availability into this component? I've read about SSIS Scale Out features but that seems more about balancing load rather than fault tolerance. I've begun reading about AlwaysOn availability groups, could that be a solution?
Any and all design advice is much appreciated. Thanks.