Why these properties matter

Advanced SQL Server connection properties exist to support real enterprise patterns without sacrificing performance, availability, or security:

• ApplicationIntent=ReadOnly directs the connection to a read-only secondary replica in an Availability Group, so your ingestion workload reads from a secondary node instead of the primary.
• MultiSubnetFailover=true tells the driver to attempt connections to the IP addresses returned by an Availability Group listener in parallel. When the Availability Group spans subnets, this delivers faster, more reliable connections during and after failover.
• Column Encryption Setting=Enabled allows the driver to transparently decrypt columns protected by Always Encrypted, where sensitive data stays encrypted both at rest and in transit and is decrypted only by an authorized client.

Each of these lives in the connection string. The challenge is getting Fabric to pass them through.

The core idea: push the properties into an ODBC DSN

The solution is to move those properties into an ODBC DSN hosted on the gateway machine, and have Fabric connect through the ODBC connector. The DSN, backed by the Microsoft ODBC Driver for SQL Server, owns the connection string, including the advanced properties, and Fabric points at it by name.

This example uses a Copy job, but the same ODBC connector and DSN approach applies to the Copy activity in a data pipeline.

There’s one important identity consideration for Always Encrypted. Decrypting Always Encrypted columns requires the calling Windows identity to hold the column master key certificate in its personal (user) certificate store. By default the gateway’s Windows service runs as NT SERVICE\PBIEgwService, which authenticates as the machine’s computer account and has no user profile where that certificate can live. To make decryption work, the gateway must run under a service account, a domain user account, or a Group Managed Service Account (gMSA), whose user store contains the certificate.

Walkthrough

Multiple technologies are used for the solution and links to the authoritative documentation for each technology are included. This post is intended to demonstrate how to put them together.

1. Set up Always Encrypted on the source

Configure your column master key, column encryption key, and encrypted columns on SQL Server. To learn more, follow the Getting started with Always Encrypted tutorial. As part of that setup, you export the column master key certificate, which you import on the gateway machine in a later step.

2. Install and configure the on-premises data gateway

Stand up an OPDG on a machine that can reach your SQL Server. Refer to the Install an on-premises data gateway documentation.

3. Create a service account

There are two choices for a service account. The first uses a dedicated Active Directory domain user account to run the gateway service, because a domain account has a real user profile and therefore a user certificate store. To create one, refer to Manage user accounts with Active Directory Administrative Center. Keep in mind that using a standard domain user account as a service account usually means setting its password to never expire, which is not recommended.

4. Change the gateway service account

Change the service account from the default NT SERVICE\PBIEgwService to the account you created. For a domain user account, use the on-premises data gateway app rather than the Windows Services console so the account receives all required privileges. For the steps, refer to Change the on-premises data gateway service account. You can’t set a gMSA through the gateway app, because you don’t know its password. Instead, stop the service, change the account, and restart it with PowerShell:

Stop-Service -Name PBIEgwService

$svc = Get-WmiObject Win32_Service -Filter "Name='PBIEgwService'"
$svc.Change(
    $null, $null, $null, $null, $null, $null,
    "CONTOSO\opdg-sql$", ""
)

Start-Service -Name PBIEgwService

5. Grant the service account access in SQL Server

Give the account that runs the gateway service the SQL Server permissions it needs to read the source database and table. Use the same account you configured for the gateway: a domain user account appears as CONTOSO\svc-opdg, and a gMSA appears as CONTOSO\opdg-sql$ with a trailing $.

6. Import the certificate into the service account’s user store

If you use a domain user account, sign in to the gateway machine as that account and import the certificate you exported earlier through Certificate Manager. A gMSA has no interactive sign-in, so you can’t log on as it to import the certificate. Instead, use PsExec from Sysinternals to open a command prompt in the gMSA’s context, and then start Certificate Manager from there. Run psexec -i -u DOMAIN\gmsaAccount$ -p ~ cmd.exe, and then run certmgr.msc in the new prompt to import the certificate. For a similar setup for SSRS using a gMSA, refer to the Always Encrypted data displayed in SSRS with a gMSA post for another example.

7. Create the DSN

On the gateway machine, create a System DSN using the ODBC Driver for SQL Server. Give the DSN a name and point it at your server, choose Integrated Windows authentication, enable Column Encryption, and, for Availability Group scenarios, set Application intent to READONLY and select Multi-subnet failover.

8. Create a Copy job using the ODBC connector

In your Copy job, choose the ODBC connector and reference the DSN in the ODBC connection string, for example dsn=opsql. Select your OPDG as the data gateway. You can leave Authentication kind set to Anonymous, because the ODBC driver named in the DSN handles authentication to SQL Server and overrides Fabric’s authentication kind.

9. Verify decryption

Without Column Encryption Setting=Enabled, the encrypted Last_Name column comes across as ciphertext. The data is protected but unreadable:

With the setting enabled in the DSN and the certificate present in the service account’s user store, the Last_Name column is decrypted and lands in plaintext:

Boundaries and things to know

• The ODBC connector requires an on-premises data gateway. In a Copy job it supports Full load and Append, as both source and destination.
• The certificate must reside in the user store of the exact account running the gateway service.
• The ODBC Driver for SQL Server must be installed on the gateway host, and the DSN must be a System DSN so the gateway service can see it.

Next steps

The ODBC connector with a DSN gives you a supported path for advanced SQL Server connection properties, including reading Always Encrypted data into Fabric. To learn more, explore the following documentation:

• ODBC connector overview
• Configure ODBC in a copy activity

The post Use advanced SQL Server connection properties in Microsoft Fabric with the ODBC connector appeared first on Ryan Adams Data Blog.

Understand Connection Recency

Connections are shared infrastructure in Microsoft Fabric. Pipelines, dataflows, semantic models, and other Fabric items use them to reach data sources without storing connection details and credentials in every item. Connection Recency adds the context administrators need to understand how each connection is being used. If you go to “Manage Connections and Gateways” in Fabric settings and open the settings of any connection, the following information card will populate:

The card provides three insights: 

• Created on shows when the connection was created. 
• Last Bound to items shows when an item was most recently associated with the connection. By any user reflects activity across the tenant, and By me is specific to the viewer. 
• Last credentials used shows when a workload most recently used the connection credentials. It also separates activity By any user from activity By me, which is the current user. 

Linking and usage are different events. A connection can be linked but never used, or it could have been linked a long time ago to an item that still runs every day. Credential-use information can also be up to 30 minutes behind real time, so use it for governance and investigation rather than immediate usage monitoring. 

Learn more in the Data source management documentation. 

Why enterprise connection management becomes difficult 

Connections can grow exponentially across a large organization. Every team, project, environment, gateway, and data source can introduce more connections. Over time, names become inconsistent, employees move on, and multiple connections can point to the same endpoint. The Fabric interface is useful for inspecting one connection, but reviewing recency and ownership one connection at a time does not scale to an enterprise inventory. 

This creates three common problems: 

• Stale connections: A connection may no longer support an active workload, but its name, creation date, and Last Bound Date do not prove that it is unused. Administrators need both Last Bound Date and Last Credential Use signals before deciding what to review or retire. 
• Duplicate connections: Different display names can hide equivalent connection definitions. Duplicates increase credential maintenance, complicate troubleshooting, and make it harder to establish a preferred connection which dilutes the advantage of connection sharing and reuse across a tenant. 
• Ownership risk: A connection with only one person as an owner can become orphaned when that person leaves the organization. The connection might then be unavailable for administration or no longer usable by dependent workloads. 

The REST APIs make it possible to evaluate these risks across every connection the caller can access. In the case of cloud connections, you can only see connections where you are the owner, but the admin for a gateway can see all connections on the gateway. This provides better scalability than the UI. 

Using the APIs to manage connections 

You can use the APIs to identify stale and duplicate connections for removal and to help mitigate the issue of single user ownership. Gateways in Fabric have a limit of 1,000 connections, so managing and governing your connections will help you stay under that limit.  

Now that we understand each of the three scenarios, let’s see what the API can provide to help us handle each scenario: 

• Stale connections:
  • LastBoundDateTime is the first of two signals we can use. If this is NULL, then it is currently not being used by a Fabric item and would be a candidate for removal. The one catch is that connections bound prior to recency being introduced will also show NULL, so we must filter out anything with a CreatedDateTime earlier than the introduction of the recency feature. Recency came out in preview at the end of March 2026, so my example code will filter anything created prior to May 1, 2026, to play it safe. 
  • The second signal we have is the LastCredentialUsedDateTime, which indicates the last time the connection was used by any item. You can change this in the example code, but I’m going to use 90 days. You might have connections that legitimately get used less frequently, such as year-end runs or only during the holiday season, so keep that in mind when reviewing and adjust accordingly. 
• Duplicate connections:
  • It is possible for two connections to have identical configurations except for the DisplayName. This defeats the purpose of having shareable connections, so we’ll need to use the LastCredentialUsedDateTime to determine which one was most recently used and flag any others to be considered for removal or consolidation. We’ll use ConnectionType, ConnectionPath, ConnectivityType, and GatewayID to identify duplicates. There are other columns you may want to add, such as CredentialType or ConnectionEncryption, depending on what you consider duplicate in your specific environment. 
• Ownership risk:
  • For ownership continuity, you want to identify connections whose only Owner role assignment is a User, then add another approved owner. This helps avoid having an orphaned connection if an owner gets deleted. Adding a Microsoft Entra group is preferred because group membership can be maintained as people join, leave, or change responsibilities. A second individual owner is better than a single owner, but it does not provide the same durable operating model as group ownership. 

Explore the List Connections, List Connection Role Assignments, and Add Connection Role Assignment documentation for the complete request, pagination, identity, and permission details.

Next steps 

Run the companion notebook first. The notebook is designed as a Python notebook to be run in the Fabric Python runtime and not the PySpark Python runtime. There will be a section for each of the previous scenarios. Stale and duplicate connections will be shown so you can review before deciding what to remove, but that part will be up to you. You can use the data frame and pass the connection ID values to the Delete Connection API. If you don’t know how to write that part, then it’s a great opportunity to use Copilot.  

The ownership section will show you all connections with a single owner where you will want to add another owner. As a best practice, we’ll assume you are adding a group, but that part will be commented out as you very likely do not want to blatantly add the same group to every connection across the board. You can use the Add Connection Role Assignment API to make those additions as you need. 

Connection recency data gives you the signals you need to manage connections at scale, but it should be the starting point for review rather than the basis for automatic removal. Use the companion notebook to identify potentially stale or duplicate connections and connections with a single owner, then apply your organization’s requirements before taking action. Regularly reviewing these signals can help you reduce unnecessary connections, improve ownership continuity, and establish a more manageable connection inventory. 

The post Manage Fabric connections at scale with connection recency in Fabric REST APIs appeared first on Ryan Adams Data Blog.

Snowflake is a common source and sink for enterprise data movement, and Microsoft Fabric Data Factory supports it in both pipelines and Copy Jobs. For organizations that restrict public network access, some additional considerations are needed to meet that requirement. You can have private inbound and outbound access to Fabric, a private staging storage account, and a private Snowflake instance.

By combining Workspace-level Private Link, a private Azure Storage account, and Snowflake’s storage integration with USE_PRIVATELINK_ENDPOINT, you can keep the path entirely private. This post explains how those controls fit together for Fabric Data Factory pipelines and Copy Jobs.

When this might apply

Your organization might require private networking, where every hop needs to be inspectable and privately routed. Regulated workloads in financial services, healthcare, and public sector regularly require that analytics platforms have no public endpoints, that inbound and outbound traffic to storage stays inside a VNet, and that any third-party SaaS integration uses private links where supported. Snowflake supports Azure Private Link for storage endpoints and Fabric supports Workspace-level Private Link for inbound workspace access which can be combined to achieve a private path. Using each control for its intended direction avoids public exposure.

The challenge

The Snowflake connector in Fabric Data Factory uses Snowflake’s native COPY command under the hood and requires an interim storage account for data staging. Currently, staging is not supported by Onelake when using Workspace-level Private Link, so an external Azure Storage account can be used instead. Staged copy supports source or destination shapes that aren’t natively compatible with Snowflake’s COPY command and can improve throughput. The catch is the Azure Storage account sits between two systems that each have their own network story: Fabric on one side and Snowflake on the other. Locking down the copy path end-to-end means securing all three surfaces — the Fabric workspace, Azure Storage staging account, and Snowflake.

The core model

Think of the topology as two independent network paths that meet at the staging account.

• Inbound Access to the Fabric Workspace — Workspace-level Private Link maps the workspace to an approved VNet and lets you deny public inbound access. It doesn’t place the Fabric workspace inside your VNet or govern outbound data-source traffic.
• Azure Blob Staging — Trusted Workspace Access provides a private connection from the Fabric Workspace to your storage account. Not only is it private, but it uses a Workspace Identity to authenticate and a Resource Instance rule to only allow specific workspaces.
• Snowflake to Staging Storage — Snowflake provisions a private endpoint into your Azure tenant against the same staging account. The Snowflake storage integration is created with USE_PRIVATELINK_ENDPOINT = TRUE so Snowflake’s own copy operations honor the private path.
• Public Internet Access — Public network access on the storage account and Fabric Workspace is blocked.

Network access and authorization are separate. Snowflake’s storage integration provisions a service principal in Azure. This SPN will need Storage Blob Data Contributor permission on the staging storage account. The Fabric Azure Blob connection uses the authentication configured for that connection, such as workspace identity. That identity will need Storage Blob Data Reader on the staging storage account.

Setup

The full configuration breaks into five phases:

1. Build the Network Foundation. Create a workspace, Azure private link service, virtual network, virtual machine, private endpoint from the workspace to the private link service and finally turn off public networking to the workspace. Complete instructions for all these items can be found in the Set up and use Workspace-level Private Links documentation.
2. Create Staging Storage Account. Use these instructions to create an Azure storage account. Once you have an account you need to configure Trusted Workspace Access for private networking between your Fabric Workspace and the staging storage account.
3. Configure the Snowflake side. In Snowflake, create a private connectivity endpoint that points at the external staging container and set USE_PRIVATELINK_ENDPOINT = TRUE. Snowflake provisions a service principal in your Azure tenant on your behalf. Grant that service principal the Storage Blob Data Contributor role on the staging account. This lets Snowflake read from and write to staging over the private endpoint using its own identity.
4. Close the public paths. You should have already denied public inbound access to the Fabric workspace as the last part of the instructions provided in step 1. You also need to disable public network access to the staging account by setting the default public network access rule to disabled.
5. Create Copy Activity or Copy Job. Create your pipeline with Copy Activity or, even better, create a Copy Job. Then select Snowflake as the source or destination, enable external staging, select the staging storage account, enter the required storage path, choose a destination, and run the item.

Boundaries and things to check

A few constraints are worth calling out before you plan a rollout:

• Workspace-level Private Link prerequisites. Confirm that your Fabric capacity SKU and region support Workspace-level Private Link, and that your tenant admin has enabled the required tenant settings.
• Staging is Azure Blob. External staging for Snowflake in the Copy Activity uses an Azure Blob container. You don’t need to enable hierarchical namespace on the account for staging to function.
• Snowflake USE_PRIVATELINK_ENDPOINT requires a supported Snowflake account. Snowflake-managed outbound private connectivity endpoints require Business Critical edition or higher. Confirm that the feature is enabled for your Snowflake account before provisioning the endpoint.
• Private Endpoint Approval. The private endpoint from Snowflake into your storage account will be in a pending state and must be approved on the storage account.

Next steps

If you’re evaluating Snowflake as a source or sink in Fabric today, deploy this topology end-to-end in a non-production workspace before you promote it. The moving parts are simple individually, but each one must be in place for the private path to hold.

Learn more from the following documentation:

• Set up and use Workspace-level Private Links in Microsoft Fabric
• Configure Snowflake in a copy activity in Fabric Data Factory
• Use workspace identity in Fabric Data Factory
• Configure trusted workspace access in Microsoft Fabric
• Snowflake: Configuring an Azure container for loading data
• Snowflake: Managing Azure Private Link endpoints

The post Connect securely to Snowflake in Fabric Pipelines and Copy Jobs appeared first on Ryan Adams Data Blog.

If you disable public access to your Azure Synapse Workspace you will get the following error message when attempting to open Synapse Studio. 

“Failed to load one or more resources due to forbidden issue, error code 403.” 

If you click on details, you’ll see the following: 

Most users end up with this error page and don’t know that disabling Public Network Access was the cause and that makes troubleshooting much more difficult.  Receiving an error after disabling Public Network Access is by design and expected.

The first thing we see is the error code being 403.  That’s an HTTP/HTTPS Forbidden error code and not a code from the Synapse service. This indicates the issue is accessing the service via HTTP/HTTPS and most likely network related. 

In this state, you might also see your SQL Pools show to be “Unavailable” in the Manage Hub. If you click OK on the error message, select the Manage Hub on the left, and SQL Pools under the Analytics Pools section you can view the status of your SQL Pools. 

Disable Public Network Access 

You can easily verify if Public Network Access has been enabled or not in the Azure portal.  Simply navigate to your Synapse Workspace resource and go to the networking section.  The very first option has a radio button indicating if Public Network Access is enabled or disabled. If it is set to disabled, you’ll see an information icon informing you that private endpoints will be required. 

It’s important to note that enabling Public Network Access is not opening your workspace up to the entire internet.  If you enable it, the service puts a firewall in front of it.  However, you do need to be aware that the default allowAll range (enabled during workspace creation) is indeed the entire internet.  You simply need to change the default rule to only allow your trusted networks, or delete it and create new allow rules.  In addition, we use cold potato routing so if all your allowed networks are in Azure then the traffic never leaves the Azure backbone. 

Create private endpoints 

If you have chosen to disable Public Network Access and are receiving the above error message when opening the portal, then you will need to create Private Endpoints to access your workspace.  This can sometimes be confusing as there are two places where Private Endpoints can be created so we need to understand the difference.  The first location is inside the workspace under the Manage Hub.  Depending on the resources you have created, you will likely see three entries already there.  These are automatically created for you so the workspace can access Storage, Dedicated Pools, and Serverless Pools. 

The important thing to remember is that these private endpoints allow the Synapse workspace to access resources outside itself. 

The second location is in the Azure Portal on the Synapse Workspace resource.  By default, this will be empty and is where you need to create private endpoints to resolve the issue in our original error message. 

The important thing to remember is private endpoints allow things outside the workspace to access things inside the workspace. 

There are three private endpoints you will want to create unless you do not plan to use a Dedicated Pool or Serverless Pool.  Each private endpoint will need to be connected to a target VNET, which should be the network location from where you plan to access your workspace. 

On the Virtual Network tab, you select the VNET from where you plan to manage your workspace and on the DNS tab, you need to select “Yes” to integrate with a Private DNS Zone. If you are already running your own DNS implementation, then can alternately create “A” records for the Private Endpoints there.

Once the “Dev” target sub-resource is created the error on the workspace portal will be resolved.  Once the SqlOnDemand target sub-resource is created, the Serverless Pool will turn from “Unavailable” to “Online”. Once the Sql target sub-resource is created, the Dedicated Pool will turn from “Unavailable” to “Online”. 

What is inside and outside of a managed workspace VNET 

Now that you know there are two places to create private endpoints depending on whether it is traffic coming into or going out of the workspace, let’s take a closer look at what is inside and outside of the Synapse Managed Workspace VNET. 

The short answer is Spark pools are inside the workspace VNET while serverless and dedicated pools are outside the workspace VNET.  This is why you get private endpoints created in the workspace automatically for serverless and dedicated pools.  Review the diagram below to get a good visualization of what this looks like.  I also highly recommend reading a post by fellow colleague Benjamin Leroux on Understanding Azure Synapse Private Endpoints. 

Accessing the private endpoint through peered VNETs 

A common practice in cloud networking is to use a hub and spoke architecture.  In this case, you will want to create your private endpoints in the hub network.  Any other VNET or spoke that is peered to the hub will be able to access the workspace.  Here is an example of what that might look like: 

When using private endpoints, you will need a private DNS zone or your own DNS so applications can resolve the endpoint names.  There is an extra step needed to link your spoke VNETs to the private DNS zone in your hub network.  Go to the private DNS zone in your hub network and select “Virtual Network Links” (You will see a link already created for the hub VNET) and then click “Add”.  Simply name your link and select the spoke network from the drop-down menu. 

Creating multiple private endpoints 

If you are not using a hub and spoke network architecture or prefer to not rely on VNET peering, there is one last choice.  You can create private endpoints in each VNET where you plan to manage your workspace.  Remember that you most likely will need to create three private endpoints (one each for the portal or Dev, Dedicated Pools, and Serverless Pools) in each VNET.  If you have 10 VNETs that will need to access the workspace, then you must create 30 Private Endpoints. 

For this scenario, you can simply follow the instructions provided above for creating the private endpoints.  You must repeat it for every spoke network that you want to have access to your Synapse workspace.  The other difference will be DNS.  Since there is no peering involved, services in spoke networks cannot access the Private DNS Zone in the hub network.  You will need to create a Private DNS zone in each network or utilize a centralized DNS. 

Here is what that might look like when utilizing private DNS zones: 

Demo

Watch this video to learn how to disable public network access.

Conclusion 

You have several options for securing network access to your Azure Synapse Workspaces. 

• Enable Public Network Access and use the built-in firewall to only allow trusted networks.  
• Use private endpoints and manage the workspace from a single VNET. 
• Use private endpoints and access the workspace through peered VNETs. 
• Use private endpoints in every VNET that needs access to the workspace. 

The post Disabling Public Network Access in Synapse appeared first on Ryan Adams Data Blog.

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the second method which uses an external table to query a path within the lake.  To learn how to use the OPENROWSET command, check out part 1 of the article series. 

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  Here you will use the Synapse Serverless Pool to query the data in your ADLS account.   

Create a data source

Go to the Develop Hub and click the ellipsis that appears when you hover your cursor over the SQL Scripts heading.  That will open a drop-down menu where you can select “New SQL Script”.   

There are three major components we need to create before we can query our table.  We need to create a Data Source, a File Format, and the External Table itself. Enter the code below into your script, make sure the “Connect To” box shows “Built-in” to use the serverless pool.  You also need to select your database in the “Use Database” box, where the metadata will be stored for everything we are about to create. This first part will create the Data Source which defines the location in the data lake where the files to be queried are stored. 

/* Create an external data source. You only do this once. */ 
CREATE EXTERNAL DATA SOURCE adls_temperature 
WITH 
(     
    LOCATION = 'https://MyLake.dfs.core.windows.net/iot/bronze/' 
); 
GO 

Create file format 

Next, we need to create a file format.  This lets the engine know what type of format it should expect for our files.  In this example, the files are in Parquet format, but you could also use DELIMITEDTEXT format for CSV type files.  There are quite a few more options required for CSV type files so check out the Full Documentation for creating an external file format. 

/* Create an external file format. You only do this once. */ 
CREATE EXTERNAL FILE FORMAT parquet_file_format 
WITH 
(   
    FORMAT_TYPE = PARQUET 
);
GO

Create external table 

Now we can finally create our table using the CREATE EXTERNAL TABLE command.  In addition to defining our columns like we would normally do when creating a table, we also have a WITH clause.  Here we input the names we used above for the Data Source and File Format along with the Location.  The root or starting folder is what we defined in the Data Source, so the Location is a relative path to that root folder.  You will also see two asterisks at the end of the location path which indicate to include every sub folder in that path.  If you do not want to include sub folders, then simply remove the asterisks. 

/* Create an external table. You only do this once. */ 
CREATE EXTERNAL TABLE Temperature( 
    [temp1] VARCHAR(100), 
    [temp2] VARCHAR(100), 
    [ArrivalTime] datetime2(7) 
    ) 
WITH ( 
    LOCATION = 'temperature_raw/**', 
    DATA_SOURCE = adls_temperature,   
    FILE_FORMAT = parquet_file_format 
);
GO

Query External Table 

Our setup is now complete, and our table is ready to query.  Note that the Data Source can be reused with a different relative path for other folders under that root.  The File Format can also be reused for Parquet files. Now it’s time to query our new External Table. 

/* Query the new external table you created. All data is still stored in ADLS */ 
SELECT temp1, temp2, [ArrivalTime] FROM Temperature 
ORDER BY [ArrivalTime] DESC; 

Demo

Learn how to read Data Lake using Synapse Serverless external tables with this video.

Conclusion 

In this article we learned how to use the Synapse Serverless engine to query data in our data lake.  This is one of two ways to perform this task.  The first method uses the OPENROWSET Command and is great for ad hoc queries.  The method described in this article allows us to persist the metadata of the configuration for easier reuse.  Now you don’t need to remember the path or format and can simply query the table like any other table in your database. To continue learning more about serverless, make sure to check out our documentation on Best Practices for Serverless SQL Pool.

The post How to read Data Lake with Synapse Serverless – Part 2 appeared first on Ryan Adams Data Blog.

There are two ways to read data inside Data Lake using the Synapse Serverless engine.  In this article, we’ll look at the first method which uses OPENROWSET to query a path within the lake. To learn how to use an external table to query a path within the lake, check out the part 2 of the article series. 

Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool).  This gives you a lot of options for ingesting, transforming, storing, and querying your data.  The article will focus on how you can use the Synapse Serverless Pool to query the data in your ADLS account.   

Let’s start by going to the Develop Hub and click the ellipsis that appears when you hover your cursor over the SQL Scripts heading.  That will open a drop-down menu where you can select “New SQL Script”.  Enter the code below into your script, make sure the “Connect To” box shows “Built-in” to use the serverless pool, and execute the script to read your data. 

In my example, I have some IoT data representing temperatures from two different probes.  The data is stored in my lake using Parquet format.  Here are the options I have defined: 

• BULK – This is the URI to my data lake and you can see I used a wildcard to grab all files ending in, .parquet (*.parquet).  If you only point to a folder or container, then it will read all files in that location. 
• FORMAT – The OPENROWSET function supports three file formats (Parquet, Delta, and CSV) and I am telling it to expect Parquet. 

The last thing I do is order my results, so the newest events are at the top.  You can easily use the following code and change it to fit your environment and start querying data in your lake using the SQL language! 

SELECT 
    Temp1, Temp2, ArrivalTime 
FROM 
    OPENROWSET( 
        BULK 'https://YourStorageAccountName.dfs.core.windows.net/iot/bronze/temperature_raw/*.parquet', 
        FORMAT = 'PARQUET' 
    ) AS [PARQUET FORMAT] 
ORDER BY ArrivalTime DESC

Demo

Learn how to read Data Lake using OPENROWSET by checking out this video. 

Resources 

You can find more details and full documentation on the OPENROWSET command with this link.  For increased performance, it’s worth noting that we have two different parsers you can use, and you define that with the PARSER_VERSION option.  Check out an excellent video demonstration we have on CSV files and the two different parser versions. 

The post How to read Data Lake with Synapse Serverless – Part 1 appeared first on Ryan Adams Data Blog.

Prerequisites 

Our focus for this post is on streaming the data in Synapse Spark so having a device to send data and the setup of IoT Hub are considered prerequisites. Their setup will not be covered in this article.

Step 1 – Create A New Notebook 

The first thing you need to do is create a new notebook in Synapse, initialize the variables, and do some setup in the first cell.  The setup code is provided below. Go to the Develop Hub and click the ellipsis that appears when you hover your cursor over the Notebooks heading.  That will display a drop-down menu where you can select “New notebook”. 

Step 2 – Setup and configuration 

The code below has comments to help you fill everything in with the service names and keys of your own services.  You need to setup your source by providing the name of your IoT or Event Hub along with the connection string and consumer group (if you have one).  Your destination will be Data Lake, so you need to supply the container and folder path where you want to land the streaming data.

There are two pieces of sensitive information that you do not want to expose in plain text, so you’ll store the key for the storage account and the Event Hub connection string in Azure Key Vault.  You can easily call these using mssparkutils once you create a linked service to your Key Vault. 

# Initialize Variables
storage_account = 'YourStorageAccountName' # This is the storage account where we will write out our data stream 
event_hub = 'YourEventHubName' # This the event hub where we will grab our stream from 
consumer_group = 'YourCGNAme' 
#This is our event hub consumer group in case we add more consumers later 
key_vault = 'YourKeyVault' #This is the name of our key valut where we will store our event hub connection string and storage account secret 

# Setup access to storage account using Key Vault Secret. Last parameter is the secret name 
spark.conf.set(f"fs.azure.account.key.
{storage_account}.dfs.core.windows.net", mssparkutils.credentials.getSecret(key_vault
,"YourKVSecretName")) 

# Setup storage locations for all data 
ROOT_PATH = f"abfss://YourContainerName@{storage_account}.dfs.core.windows.net/" # Put your container before the @ sign 
BRONZE_PATH = ROOT_PATH + "bronze/" #Folder where we land our raw data stream 
CHECKPOINT_PATH = ROOT_PATH + "checkpoints/" #Folder to store our checkpoints in case the stream gets broken 

# Get Event Hub Connection String from Key Vault 
IOT_CS = mssparkutils.credentials.getSecret(key_vault,'YourHubConnectionStringSecret
Name') # IoT Hub connection string (Event Hub Compatible) 
#print(IOT_CS) 

# Setup Event Hub Dictionary with Config Settings 
ehConf = {  
  'eventhubs.connectionString':sc._jvm.org.apache.spark.eventhubs.EventHubsUtils.encrypt(IOT_CS),   
'ehName':event_hub,   
'eventhubs.consumerGroup':consumer_group }

Step 3 – Start fresh and wipe out ADLS 

In case you want to run this action multiple times, you can delete everything in your ADLS container for a fresh start. 

# Make sure root path is empty
mssparkutils.fs.rm(ROOT_PATH, True) 

Step 4 – Start streaming the data 

Now that you have everything setup and configured it’s time to read the data stream and then write it out to ADLS.  First you create a data frame that uses the Event Hub format and our Event Hub Configuration created in Step 1. The next part gets a little tricky because the entire JSON payload of the stream is stored in a single column called “body”.  To handle this, we create the schema we intend to use when landing the data in a final data frame.  Last, we write the data out to ADLS in parquet format. 

from pyspark.sql.functions import * 
from pyspark.sql.types import * 

# Create a DF and load it from the EH stream 
df = spark.readStream.format("eventhubs").options(**ehConf).load() 

# The stream stores the JSON payload in a single "body" column. 
# We need a schema to match and extract the JSON 
Schema1 = StructType([StructField("temp1", StringType(), True), 
                      StructField("temp2", StringType(), True) 
                    ]) 

# Extract the JSON and enqueued time 
dfRaw = df.select(df.body.cast('string'), df.enqueuedTime.alias('ArrivalTime')) 
dfJson = dfRaw.select(from_json(dfRaw.body, schema=Schema1).alias("json"), dfRaw.ArrivalTime) 
dfFinal = dfJson.select("json.*", dfJson.ArrivalTime) 

# Write the stream to ADLS 
# We have also enabled checkpoints so if the stream fails we can pick right where we left off 

dfFinal.writeStream.format("parquet").outputMode("append").option("checkpointLocation", CHECKPOINT_PATH + "temperature_raw").start(BRONZE_PATH + "temperature_raw") 

Step 5 – Read the data streamed to ADLS with Spark Pool 

Now that we have the data streaming to ADLS in parquet format, we are going to want to read it and validate the output.  You could use the Azure portal or Azure Storage Explorer, but it would be much easier to do it right here using Spark in the same notebook. This part is super easy! We simply configure a few variables for connecting to our ADLS account, read the data into a data frame, and then display the data frame.

from pyspark.sql import SparkSession 
from pyspark.sql.types import * 

# Primary storage info account_name = 'YourStorageAccountName' # fill in your primary account name 
container_name = 'YourContainerName' # fill in your container name 
relative_path = 'bronze/temperature_raw/'  # fill in your relative folder path 

adls_path = 'abfss://%s@%s.dfs.core.windows.net/%s' % (container_name, account_name, relative_path) 
print('Primary storage account path: ' + adls_path) 

df_parquet = spark.read.parquet(adls_path) 
display(df_parquet)

Conclusion 

Spark Pools in Azure Synapse support Spark structured streaming so you can stream data right in your Synapse workspace where you can also handle all your other data streams.  This makes managing your data estate much easier.  You also have the option of four different analytics engines to suit various use-cases or user personas. 

The post Structured Streaming in Synapse Spark appeared first on Ryan Adams Data Blog.

Always Encrypted protects our data both at rest and in transit. To accomplish that, SQL only stores the encrypted data and cannot decrypt it; all the work is done by the client. In our case the client is SSRS and it is the account running the SSRS service that will need the certificate to decrypt data. Note that it is not the account running the report.

Certificate Stores

When configuring Always Encrypted, we have a choice between two key stores. In our example we will use the Windows Certificate Store. You can also use Azure Key Vault, but direct access with that method is not supported with SSRS. You could use the Windows Certificate Store, export the certificate, and import it into Azure Key Vault. However, SSRS cannot directly access Azure Key Vault.

The certificate is important because we must export it so we can later import it to our clients. Best practice is to run the Encrypt Column Wizard in SSMS from a server that is not the server hosting your data. This prevents us from accidentally leaving the cert on the server where if it gets compromised the attacker could get both the data and the certificate.

Always Encrypted Wizard

If you run the wizard from the client or application server, you have two choices. The first is to run SSMS as an administrator so you can store the certificate in the local machine store. This allows any application on the server to use the certificate if you give it permissions to do so. If you do not run SSMS as an administrator, you will only see the option to store the certificate in the local user store for the current user.

For our example we will use the Current User store to comply with the principal of “Least User Privilege“. Once we complete the Always Encrypted wizard, you can open Certificate Manager and export the cert making sure to include the private key.

Always Encrypted Certificate Import

We have our column encrypted and have exported the certificate. Next, we need to import it on the SSRS server. This is where the fun begins. You might be tempted to just import the certificate into the local machine store instead of the user store so all applications can use it. This does not work, and you will have to import into the user store for the account running the SSRS service. Normally you could right click the certificate manager and select “Run As” to be in the context of the account running SSRS. This does not work with a gMSA because you don’t know the password.

Get Creative

It’s time to get creative and break out some SysInternals tools. We are going to use PSExec to open Certificate Manager under the credentials of our SSRS gMSA account. We start by opening an administrative command prompt and executing the following from the location where we downloaded the SysInternals tools.

PSExec64.exe -i -u contoso\ssrs_svc$ -p ~ cmd.exe

The first parameter -i is needed since we will be launching another command prompt interactively on the desktop. The second parameter -u provides our username and, in our case, the gMSA running the SSRS service. Remember that gMSA accounts need a $ sign at the end. The third parameter -p is the password for the gMSA account, but we do not know what it is. The ~ is allowed in place of the password. The last thing we provide is the executable we want to run. For us it will be another command prompt. Ideally, we would like to just run certmgr.exe from here, but that does not work.

Once you hit enter another command prompt will open where we can enter certmgr.exe to open the Certificate Manager under the context of our gMSA account. Now we can finally import the certificate.

View Your Reports

That’s it! Now you can go open your report in SSRS and see your Always Encrypted data displayed. These are some great security technologies, but it takes some outside of the box ideas to get them working together correctly.

For more SQL technology intersections check out my post on Configure SQL Server Failover Cluster Instance On Azure Virtual Machines With MSDTC.

The post Always Encrypted Data Displayed in SSRS with a gMSA appeared first on Ryan Adams Data Blog.

• Part 1 – Building the environment. Hyper-v VMs, Active Directory, DNS, and Routing and Remote Access Services
• Part 2 – Windows Failover Clustering. Cluster validation, networks, and quorum
• Part 3 – Availability Group. Dedicated network and AD permissions
• Part 4 – Failover Cluster Instance. Creating shared storage using iSCSI and adding the storage to the cluster.

Network Configuration

Here is the list of IP addresses from Part 1 that you will need to configure the SQL Server FCI.

Architecture

As a refresher, here is the diagram of the lab you are building.

Build Failover Cluster Instance Video

The post Build a SQL Cluster Lab Part 4 appeared first on Ryan Adams Data Blog.

• Part 1 – Building the environment. Hyper-v VMs, Active Directory, DNS, and Routing and Remote Access Services
• Part 2 – Windows Failover Clustering. Cluster validation, networks, and quorum
• Part 3 – Availability Group. Dedicated network and AD permissions
• Part 4 – Failover Cluster Instance. Creating shared storage using iSCSI and adding the storage to the cluster.

Create Databases Code

USE [master];
GO

DROP DATABASE IF EXISTS App1AG_DB1;
GO

DROP DATABASE IF EXISTS App1AG_DB2;
GO

DROP DATABASE IF EXISTS App1AG_DB3;
GO

CREATE DATABASE [App1AG_DB1]
 ON  PRIMARY 
( NAME = N'App1AG_DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

CREATE DATABASE [App1AG_DB2]
 ON  PRIMARY 
( NAME = N'App1AG_DB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

CREATE DATABASE [App1AG_DB3]
 ON  PRIMARY 
( NAME = N'App1AG_DB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB3.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB3_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB3_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO


--Full backup required to create an AG
BACKUP DATABASE [App1AG_DB1] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB1.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

BACKUP DATABASE [App1AG_DB2] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB2.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

BACKUP DATABASE [App1AG_DB3] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB3.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

Create Availability Group Code

/*
Author: Ryan Adams
Website: https://www.ryanjadams.com/
Twitter: @ryanjadams

This script must be executed in SQLCMD mode.  This script was designed to setup an AlwaysOn Availability Group
in a custom lab environment.  Use at your own risk and DO NOT run this in production.  Make sure you read and understand
it thoroughly.
*/

/* First we connect to each replica in order and create logins for the account running the SQL Server Service on the other replicas */
:Connect NODE1

USE [master]
GO

CREATE LOGIN [contoso\svc-sql2] FROM WINDOWS
GO

CREATE LOGIN [contoso\svc-sql3] FROM WINDOWS
GO


:Connect NODE2

USE [master]
GO

CREATE LOGIN [contoso\svc-sql1] FROM WINDOWS
GO

CREATE LOGIN [contoso\svc-sql3] FROM WINDOWS
GO


:Connect NODE3

USE [master];
GO

CREATE LOGIN [contoso\svc-sql1] FROM WINDOWS;
GO

CREATE LOGIN [contoso\svc-sql2] FROM WINDOWS;
GO

/* Next we connect to each replica and create the endpoint.
We also grant connect permission on the endpoint to all of the other replica service accounts. */
:Connect NODE1

USE [master];
GO

CREATE ENDPOINT [AlwaysOn_EP]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.1.1)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to its own NIC card */
	FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
		, AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
		, ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql2];
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql3];
GO

:Connect NODE2

USE [master];
GO

CREATE ENDPOINT [AlwaysOn_EP]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.1.2)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to it's own NIC card */
	FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
		, AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
		, ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql1];
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql3];
GO


:Connect NODE3

USE [master];
GO

CREATE ENDPOINT [AlwaysOn_EP]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (172.18.0.3)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to it's own NIC card */
	FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
		, AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
		, ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql1];
GO

GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql2];
GO

/* Here we connect to each replica and ensure that the AlwaysOn extended events session is set to run at startup and that it is also started */
:Connect NODE1

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect NODE2

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect NODE3

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

/*
There will be 3 databases included in our AG and they currently exist on NODE1.
Prior to SQL 2016 we would have to do the following:
	Backup each database data file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause
	Backup each database log file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause

With SQL 2016+ we can use Direct Seeding instead.
*/

/* Here we connect to our primary replica (NODE1) and create our AG. */
:Connect NODE1

/* We can use trace flag 9567 to enable compression for the VDI backup for the seeding process */
DBCC TRACEON (9567, -1);
GO

USE [master];
GO

CREATE AVAILABILITY GROUP [App1AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, FAILURE_CONDITION_LEVEL = 3, HEALTH_CHECK_TIMEOUT = 30000, DB_FAILOVER = ON, DTC_SUPPORT = PER_DB, CLUSTER_TYPE = WSFC) /* You can also use SECONDARY_ONLY, PRIMARY, or NONE
Failure condition levels are from the least restrcitive of 1(SQL Service is down) to 5(Exhaustion of worker threads). 3 is the default.
Health check default is 30 seconds, represented in milliseconds. This is how long we wait for sp_server_diagnostics to return. */
FOR DATABASE [App1AG_DB1], [App1AG_DB2], [App1AG_DB3]
REPLICA ON N'NODE1' WITH (ENDPOINT_URL = N'TCP://NODE1.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
	N'NODE2' WITH (ENDPOINT_URL = N'TCP://NODE2.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
	N'NODE3' WITH (ENDPOINT_URL = N'TCP://NODE3.contoso.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 70, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC);
GO

/* Here we create a listener for our AG. If you have issues creating the listener check permissions in AD.
	You might also have to turn the AG networks to client and cluster and then turn them back to none post-listener creation*/
ALTER AVAILABILITY GROUP [App1AG]
ADD LISTENER N'App1AG' (
WITH IP
((N'10.0.0.11', N'255.255.255.0'),('172.16.0.11','255.240.0.0'))
, PORT=1433);
GO

/* Now that the AG exists we connect to each secondary replica and join it to the group
	We also have to grant the CREATE ANY DATABASE permission so Seeding can create the DBs */
:Connect NODE2

ALTER AVAILABILITY GROUP [App1AG] JOIN;
GO
ALTER AVAILABILITY GROUP [App1AG] GRANT CREATE ANY DATABASE;
GO

:Connect NODE3

ALTER AVAILABILITY GROUP [App1AG] JOIN;
GO
ALTER AVAILABILITY GROUP [App1AG] GRANT CREATE ANY DATABASE;
GO

--This query allows us to view seeding performance statistics
SELECT * FROM sys.dm_hadr_physical_seeding_stats;

/* Now we need to turn our trace flag back off */
:Connect NODE1
DBCC TRACEOFF (9567, -1);
GO

Network Configuration

Here is the list of IP addresses from Part 1 that you will need to configure the AG.

Architecture

As a refresher, here is the diagram of the lab you are building.

Build Availability Group Video

The post Build a SQL Cluster Lab Part 3 appeared first on Ryan Adams Data Blog.