Microsoft Azure SQL Database provides a relational database service for Azure and other Internetbased applications. To help protect your data, firewalls prevent all. Replicate Microsoft Dynamics 3. Microsoft Azure SQL Database. By using the Data Export Service, when you activate a data export profile from within Microsoft Dynamics 3. Azure. The initial synchronization includes all the data associated with the entities added to the export profile, but thereafter synchronization includes only new changes, which are continuously sent to the Data Export Service. Data sent to the Data Export Service is stored temporarily in Azure Service Bus and Azure Storage, processed in Azure Service Fabric, and finally synchronized inserted, updated, or deleted to the destination database specified in your Azure subscription. After the data has been synchronized, it is deleted from Azure Service Bus and Azure Storage. Microsoft Access Current Database Name Sql Server' title='Microsoft Access Current Database Name Sql Server' />If there is a failure during data synchronization, minimal data corresponding to entity type, record ID, and sync timestamp is stored in Azure Storage to allow for downloading a list of records that were not updated. An administrator can deactivate the data export profile at any time to stop data synchronization. In addition, an administrator can delete the export profile to remove any failed record logs and can uninstall the Data Export Service solution to stop using the Data Export Service. Data synchronization happens continuously between Dynamics 3. Data Export Service in a secure manner. Data is encrypted as it is continuously exchanged between Dynamics 3. Data Export Service. Azure components and services that are involved with the Data Export Service are detailed in the following sections. Note For more information about additional Azure service offerings, see the Microsoft Azure Trust Center. Learn how to import or link to data from an SQL Server database into Access. Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name DSN using the OLEDB driver, created a System DSN for the new SNAC. Azure Service Fabric. This provides the API and compute Azure VMs to process record synchronize notifications received from Dynamics 3. Micro services that are deployed on virtual machines managed by the Azure Service Fabric runtime handle all the compute services related to data synchronization. Azure Service Bus. This provides the message bus into which Dynamics 3. Azure Service Fabric. Each message stores information, such as the org id and record, for which for which to sync data. Data in the Azure Service Bus is not encrypted at rest, but is only accessible by the Data Export Service. Azure Blob Storage. Data is temporarily stored in Azure Blob Storage in case the record sync notifications data is too large to store in a message or a transient failure is encountered to process the synchronization notification. These blobs are encrypted by leveraging the latest feature in the Azure Storage SDK, which provides symmetric and asymmetric encryption support and integration with Azure Key Vault. Azure SQLThe Azure SQL Database stores data export profile configuration and data synchronization metrics. Microsoft Access Pass Through Queries to SQL Server. Problem. Thus far in this tips series on Access and SQL Server we have created an ODBC Data Source Name DSN using the OLEDB driver, created a System DSN for the new SNAC SQL Native Client driver and created linked tables in Access by using the SNAC DSN. At this point you can use the linked tables as you would any other native Access table. However there are potential issues. When querying a linked table, Access will returnload the entire table into Access before parsing the WHERE or HAVING clauses. Imagine the implications of querying a large table or data set comprised of multiple tables in Access against a linked SQL Server record source in your environment  There has to be a better way Solution. Of course there is. Microsoft Access gives you the ability to issue the query created in Access directly against the SQL Server database, using T SQL commands. This is called a Pass Through Query in Microsoft Access. By the time you have finished reading this tip you will know precisely how to create one. If you have been following along with this tip series on Microsoft Access and SQL Server integration then you should have an Access database with the following linked tables to an instance on your SQL Server for the 2. Northwind database. The Tables listing should look something like what you see below. Lets take a minute to create a basic Access query against the dboOrders, dboCustomers, and dboEmployees tables first. Select Queries under the Objects menu on the left side of the main Access interface. You should see a form similar to what is displayed below. Select the Create query in Design view option on the right pane of the window. Once the Show Table interface displays, then select the three tables dboOrders, dboCustomers, and dboEmployees. Next, click the Add button to begin designing the query. What you will see next is a query screen that looks something like what is presented below. I have altered the tables graphical placement and size for presentation purposes, but your screen should be similar. The Access Query Designer is very similar to the one you find incorporated into SQL Server. Double click the fields as follows in order to load them into the query dboOrdersdboCustomersdboEmployees. Select to sort the results by Company Name and to limit the results to only those where the Ship Country is USA. The Access Query Designer should look something like this Now if you run the query as is via either selecting Query Run from the menu bar or by clicking on the button the following results are displayed. So, you may ask, What is wrong with this query  Well, it all comes down to what I mentioned earlier  Access will load the entire data set prior to filtering the data and returning the results. Using our example, this means that the complete Orders, Customers, and Employees tables will be returned from SQL Server into Access. Afterwards, the tables will be joined and the querys WHERE clause will be applied, limiting the results to only those records where Orders. Ship. Country USA. Furthermore, Access is doing all the processing. All the processing power of your SQL server is disregarded the client workstation where Access is being run is bearing the brunt of all processing. Microsoft Access Pass Through Queries. This is where the Pass Through query comes into play. Lets first take a look at how to convert this query into a pass through query first and then I will explain what this accomplishes. To convert the query into a pass through query, select Query SQL Specific Pass Through from the menu bar. In doing so you will see that the query view switches to SQL text. Once you convert the query into a pass through query, design view is not available. This should be familiar ground though for the experienced or novice SQL Server DBA. The query text does look a little different from what youre accustomed to. Try running it and see what happens So, what is wrong  Well if you convert an existing Access query into a pass through query, it will use the proprietary Access SQL language, not T SQL. Cost To Install Plywood Underlayment Glue more. This is an issue, since we are passing the query back to the SQL Server for parsing and processing. This means that the query must be written in the T SQL language. Therefore you have two options  convert the SQL Server code to T SQL or create the query as a pass through query from the start. Converting the SQL Server code from Access to T SQL typically comes down to replacing the underscore from the table name with a period dboOrders in our example will become dbo. Orders and replacing double quotes, signifying a text value, to single quotes. Creating the query as a pass through from inception simply means that you select Query SQL Specific Pass Through from the menu bar prior to adding tables to the query while in design view. What I tend to do is build the query I want to run in SQL Server Management Studio first. I then paste the query text into the Access pass through query. This resolves two dilemmas  it allows reviewing the estimated execution plan, and it confirms that the query will run successfully. Below Ive done just that. Ive converted the existing query into T SQL. The results are the same, but Ive accomplished two things. Ive forced the query to execute on the SQL Server, not the client workstation. Ive also eliminated the Access default process of loading the complete record set into the workstation prior to processing the query. I can now save this query and use it as the record source for an Access form or report. Essentially, I can use this query as if it is native to the Access database. Next Steps. Last Update 2. About the author. Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1. View all my tips.