none
Using stored procedure to write into an Azure SQL database from another Azure SQL database

    Întrebare

  • Hi,

    I have 2 Azure SQL database, AzureDB1 and AzureDB2: can I write into AzureDB2 a stored procedure that reads data

    from AzureDB1 and writes them into AzureDB2?

    Many thanks

    vineri, 20 aprilie 2018 20:49

Toate mesajele

  • Hello,

    You can select data from another database by using elastic queries and external data sources as explained on the following article. Once you query that remote database you can write data on the current database.

    https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    vineri, 20 aprilie 2018 21:03
  • Hi Alberto, thanks for your reply, but the elastic query feature is actually in preview.

    Moreover, in the Azure destination database can I write a stored procedure that reads data from the Azure source destination and then schedule the resulting stored procedure?

    Thanks

    luni, 23 aprilie 2018 10:22
  • Preview does not mean that you cannot use it - it is tagged as Preview to let you know that the features are still under final development and may change slightly from what the current behaviour is and also that the pricing might be less expensive than when it goes to GA - therefore you CAN go ahead and use it.

    You cannot communicate across SQL DBs without Elastic Query, so no you cannot just create a stored procedure in one DB and query the other.

    If you want to schedule it then have a look at Elastic Jobs - this will allow you to schedule it to run and use Elastic Query to get the data from the other DB


    Martin Cairney SQL Server MVP

    luni, 23 aprilie 2018 12:01
  • Hi Martin, thanks for you reply, but for my (wrong?) knowledge it isn't a good practice using Azure previews for a prod environment.

    Now, Elastic Jobs should represent one of the Elastic database features: do I need to pay a separate feature/service respect to an Azure SQL database as a service in order to use these Elastic features?

    Thanks

    luni, 23 aprilie 2018 12:27
  • There is nothing wrong with using preview features in production, just make sure you keep up to date with any changes that may happen. You will still get supported for issues.

    For the Elastic Jobs - yes there is some additional costs. See https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-jobs-overview#components-and-pricing for the details.


    Martin Cairney SQL Server MVP

    luni, 23 aprilie 2018 12:33
  • So, do I need to pay:

    - an Azure cloud service,

    - an Azure service bus,

    - an Azure storage account,

    plus an ad-hoc Azure SQL database?

    Do I need to have a 3-rd Azure SQL database aside from the AzureDB1 and AzureDB2 databases for implementing Elastic jobs? 

    Are there any components to pay to use elastic query?

    In order to use elastic query and elastic jobs, do I need to create a SQL elastic pool for AzureDB1 and AzureDB2?

    Thanks

    luni, 23 aprilie 2018 13:21
  • Hi pscorca,

    Elastic query functionality is part of Azure SQL Database pricing. You don't need an Azure Cloud Service, nor a Service Bus, or an Azure Storage Account to perform basic elastic queries between two Azure SQL Databases.

    This blog post explains how simple it is: Cross-Database Queries in Azure SQL Database

    Here is some additional reading: Azure SQL Database elastic query overview (preview)

    I hope this provides some clarity.

    Regards,

    Mike

    vineri, 4 mai 2018 18:06
    Moderator
  • Hi,

    I'm organizing better the scenario to face.

    This picture could represent the case:

    I think:

    a. to create the external data source and the external tables inside the Az_DB_02 database. Each external table should map the corresponding source table inside the Az_DB_01 database;

    b. to create the ETL stored procedures on the Az_DB_02 database, to read data from the Az_DB_01 database and to write them on the Az_DB_02 data mart, writing elastic queries inside the stored procedures;

    c. to create one or more elastic jobs on the Az_DB_02 database to invoke the stored procedures to feed the data mart.

    Is the above working procedure a good approach to the reference scenario?

    Moreover, reading about elastic queries I don't understand the reason to accomplish a partitioning (vertical or horizontal) task to my reference scenario.

    Thanks

    luni, 14 mai 2018 09:19
  • The purpose of the vertical partitions is to establish the external data for each remote database. In this section Vertical partitioning - cross-database queries, it explains that the vertical partitions are the reference data that will be written or read by the remote databases. 

    Since you are copying data from one DB to the other, the concept of vertical partitions can not be fully realized but, in this scenario it is a workable solution. 

    Regards,

    Mike

    luni, 14 mai 2018 22:07
    Moderator
  • Hi Mike,

    my goal is to read data from an Azure SQL database as a source to feed a data mart or a little data warehouse on another Azure SQL database.

    Now, is using elastic query feature the right manner to face a such scenario? Or is it better to use another Azure component like e.g. ADF or another one?

    Thanks

    miercuri, 16 mai 2018 09:06
  • Hi Pscorca,

    Is the need to read data from the other database something that occurs during normal operation of your solution or, is this data movement part of a maintenance window? If this is during normal operation of your solution then elastic queries is the best option. 

    Regards,

    Mike

    Note: If this information answers your question, please mark this as answered so as to assist others looking for the same information.

    miercuri, 16 mai 2018 19:50
    Moderator
  • Hi Mike,

    I should implement an ETL solution to feed a data mart (Az_DB_02 database) or a little data warehouse from a data source (Az_DB_01 database), on a daily basis. For a such purpose, I could create some T-SQL stored procedures or a set of SSIS packages, to run in a scheduled manner. Moreover, further I could read data from different data sources, e.g. Facebook or Google.

    Now, is it always a proper solution reading data by using the elastic query or does it occur some other Azure components?

    Thanks


    • Editat de pscorca luni, 21 mai 2018 11:33
    luni, 21 mai 2018 11:24