nice work! Very detailed, very interesting!
Only thing that irritated me in terms of processes: We'd have a single point of failure in the external table; at least the assigned database might fail in such an availability group. What was the scenario: OLAP, OLTP?
Perhaps you might want to differentiate the passwords (you're using always "password@123" which is at least for me a bit misleading):
- The password used when creating the DATABASE MASTER KEY is used to encrypt the database master key.
- The SECRET specified when creating the DATABASE SCOPED CREDENTIAL is, together with the IDENTITY, to my understanding part of the authentication of a host operating system or a platform.
- When specifying the EXTERNAL DATA SOURCE, the option CONNECTION_OPTIONS contains a UID and a PWD to authenticate against an application.
Idea: It might be more intuitive to use different passwords (e.g. P@$$w0rd_database_master_key, P@$$w0rd_database_scoped_credential, P@$$w0rd_external_data_source).
What do you think?
If you set out to do something, something else must be done first.