Azure SQL Database Requirements

the activate sql database can be installed onto azure sql this is only recommended when the activate server is also installed in azure the activate server should only be installed into the cloud when the major systems that activate is managing are also migrated an active directory dc should be available in the azure network migrating activate to an azure vm can cause performance issues for network heavy tasks for example, large folder scans to on premise file server, querying on premise sql or other line of business applications that remain on premise sql server options there are a number of options for using sql in azure available azure virtual machine with sql server installed this is the closest to a current on premise database the vm can be domain joined to your active directory and provides a very similar environment to a current on premise sql server this is recommended if you have a large sql activate database and require more control over the operating system level azure sql managed instance this provides a managed instance of sql in the cloud without allowing access to the operating system the system can be installed with the same collation and timezone as your current environment the timezone is important and can only be set when creating the instance it cannot be changed please ensure that this is set the same timezone as your current on premise server the default is utc and must be changed to your local timezone azure sql database azure sql database can be used as a hosting platform for activate; however, it is not currently recommended customers choosing this option must acknowledge and accept the following limitations and responsibilities as azure continues to develop acti sql limitations azure sql database has several functional limitations compared to a full sql server instance activate does not rely on these limited features out of the box microsoft documents the feature differences https //docs microsoft com/en us/azure/azure sql/database/features comparison?view=azuresql the customer confirms that none of these restricted features are in use today and there is no plan to use them in future utc times azure sql database enforces utc as the system time zone and this cannot be changed all activate functions must use dbo getlocaldate() instead of the built in getdate() function this function converts utc to the local timezone of the application server any custom scripts, reports, or queries must be reviewed and updated to ensure they use the correct date/time handling performance azure sql database does not support sql profiler, which makes performance diagnostics significantly more challenging activate cannot guarantee or actively monitor performance on this platform support is provided on a best effort basis if performance issues arise, activate may recommend migrating to a different sql server platform dba support although azure sql database is a managed service, the database itself still requires dba oversight customers must provide sql dba support due to platform limitations, dba effort is often higher , not lower, than with a full sql server instance azure permissions activate must be granted sufficient azure permissions to fully support the database if adequate permissions are not provided, activate support may be limited migration migrating to azure sql database (or azure sql managed instance) involves a complex database migration , not a simple detach/reattach process microsoft documentation is https //learn microsoft com/en us/azure/azure sql/migration guides/database/sql server to sql database guide?view=azuresql the migration process and responsibility sit with the customer cutover and go live processes are longer and more complex compared to traditional sql server authentication limitations activate studio desktop users must authenticate using azure ad username and password when connecting to the database further details follow below sql authentication activate normally uses windows integrated authentication to connect to sql server therefore the activate web portal and activate orchestrator connect as the configured service accounts any users running activate studio will connect as themselves this is normally done automatically because they are added to the 'activate admin' group which grants connection rights to the sql database for azure sql managed instance or azure sql database we use azure ad authentication with a specified azure username and password the user name and password is stored encrypted using windows system protect into the registry this username and password are used in conjunction with the connection string to connect to the database this user must be granted logon rights to the sql database therefore the activate web site and activate job service connect to sql as this account users running activate admin on the server will also connect as the azure sql account rather than using windows integrated security however, once connected the activate system will ensure that the user only has the rights in activate that their windows user account has based on the roles they belong to in activate