@AlbertoMorillo Thanks for the proposed answer. I checked it out and you are right with the DTU premium tier, there are free readable replicas. However the DTU triples and so does the cost per month. Please advice on the cheapest way to achieve this. The database is quite and sized on a Standard S2 with 50 DTU's allocated. Is the the best configuration from a cost perspective ?

@AlbertoMorillo
Hello.

I managed to get the scale out read only option working, however as documented the ApplicationIntent =ReadOnly needs to be specified, this is good for a reporting tool etc, however for users who connect via SSMS one can be sure that they will always forget to specify the read intent. I want a way whereby access to the database can be denied, and users can only use the read only replica. With the read scale out on premium tier, this cannot be done as access is still via the database server, the main change is the ApplicationIntent =ReadOnly flag.

The only other option i see is using data synch. The immediate issues I see with data synch are as follows.

• All tables require a primary key.
  

• What happens is the schema of the table on the primary database is altered, adding indexes, adding/editing constraints etc, will this break data synch ?
  

• If a new table is added to the database, it needs to be added to the synch group.

Are there any other read only database options where a seperate database connection can be provided ?

This wouldn't work. The idea of killing off users.
Are there any other options ?

Hi

The VPN doesnt get connected at all, it fails the connection as it complains that it cannot find the host. This is probably because of DNS issues as the VPN gateway is mentioned in the VPN configuration which it cannot resolve. Ironically its Azure VPN that causes the DNs issues, when i open up Azure VPN on a different device, it works perfectly and as mentioned before it was working perfectly on this device prior to the reset of the network devices and the removal of all network devices on the laptop (I removed every device under connections Wan miniport, Wifi, ethernet etc).

Thanks for the proposed answer, just to clarify I am not having DNS issues with the P2S VPN, I am having issues with my computer device and DNS anytime a connection is initiated from the Azure VPN app. I removed the custom DNS forwarder information which could be found within the xml configuration file still doesnt work, it appears that the VPN connection kills my my default DNS configuration and the only way to resolve this is to restart the device. This wasn't the case prior to the changes I made to network adapters by removing them all and restarting the device this then adds them back ever since I did that, my DNS gets corrupted anytime the Azure VPN app tries to initiate a connection even though it fails.

Thanks for the proposed answer, I figured out the domain name issue. There are 2 domains, I created accounts in both domains and tried to login. I now get the error. The logon attempt failed. I I downlod the RDP file and edit the file by adding the following lines.

 full address:s:rdp_virtual_machine_name:3389
 enablecredsspsupport:i:0
 authentication level:i:2
 enablecredsspsupport:i:0
 authentication level:i:2

The sign in method you're trying to use isn't allowed. Try a different sign in method or contact your administrator. This is after disabling MFA.

Can you please clarify, the device that i am trying to login from is not AAD enrolled and not on the domain either. From reading the below
Remote connection to VMs that are joined to Azure AD is allowed only from Windows 10 or later PCs that are Azure AD registered (starting with Windows 10 20H1), Azure AD joined, or hybrid Azure AD joined to the same directory as the VM.

It appears as though it is not going to work, can anyone please clarify. All I want to be able to do is use AAD accounts and not using a local administrator account, what would I need to go to allow AAD authentication ?

Thanks for the reply.
I enabled AAD authentication and the VM got registered as a device in AAD.
I ran dsregcmd /status.

I can see

+----------------------------------------------------------------------+
| Device State |
+----------------------------------------------------------------------+

          AzureAdJoined : YES
       EnterpriseJoined : NO
           DomainJoined : NO

As the machine is a production one, I have a windows 100 vm where I can log login/authenticate via AAD login. Here NLA also disabled, Looks like the device has joined AAD, ran DSRegCmd /Leave to remove it from AA then rebooted. Tried the AAD authentication, still failing. I thought the AAD extension was required in order to use AAD login.

Login with AAD is enabled.
I still cannot get it to work its not clear what i am missing.

Please note that the virtual machine will not host any application, but rather a DNS forwarder. I was thinking of 2 virtual machines sites in 2 different regions.

Hello.

I have configured the P2S VPN connection.

However I do have some constraints.

How can I allow users to connect to SQL server using Azure AD authentication whilst connected via the VPN.

I got SQL login working using the username in the format sqlLOGIN@mydb.database.windows.net, however what I want to get working is Azure AD with MFA. If I use the vnet IP address which works for SQL authentication, and select Azure AD authentication with MFA, I get the error Cannot open server "10.1.1.x" requested by the login. The login failed. Microsoft SQL server Error 40532.

One of my objectives is to provide connectivity to SQL without having to allow the public IP address of several users.
Is there a way to have a friendly name for connectivity rather than using the IP address on the vNet.

Please note that there is no vNet currently for SQL.

Thanks for this, I read through and I could not see where serverproperty tells me the nodes in the cluster and thier connection details for example, server and port number. Please clarify.

That tells me the node name of where SQL is running, I am looking for all nodes in the cluster not just the active/where i am connected to.

Hi,

Thanks for the snippet of code provided, this has given me a better understanding and perhaps a reason to try out a different approach.

The new approach is this.

1. Get a list of decommissioned servers from the database and load it into an array/custom object.
   

2. Import the csv file into an array.
   

3. Remove any matches from the csv file import that matches the decommissioned list and save this into a new array/custom object.
   

4. My preference is to not use a database for comparison, hence this approach.

As per the details above, table file etc. I couldn't get this to work.

  $data = @{}
  Import-Csv $file -Header 'connection_string' |
      Foreach-Object {
          $data[$_.connection_string]  
      }
  $filtered_list = $decomissioned_list |
      ForEach-Object{
          if (!($data.ContainsKey($_))){
              [PSCustomObject]@{
                  servername = $_
              }
          }
      }