Does Streach database cut cold data from localdatabase and transfers it to azure ?

rajesh yadav 171 Reputation points


In Stretch Database, I want to confirm that cold data which is transferred from a local database to azure database exists at both ends, or it is cut from local and then transferred to azure database?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,853 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
496 questions
{count} votes

Accepted answer
  1. 4,301 Reputation points

    Yes, you will get the historical data, since SQL Server uses a special type of linked server to "stretch" out your query so it incorporates data from both the local and the remote table. Basically, a UNION between the local and the remote table.

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. 4,301 Reputation points

    The "cold" data is removed from your local SQL Server instance.

    0 comments No comments

  2. rajesh yadav 171 Reputation points

    That means the query I am running on the local database will not get historical data?

    0 comments No comments

  3. Tom Phillips 17,716 Reputation points

    Please see:

    The table is online and ready to query, and you don’t have to change existing queries or applications—it’s completely transparent.

    0 comments No comments

  4. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

    Hi rajeshyadav-0048,

    Agree with others. In addition, you can add the WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = LOCAL_ONLY|REMOTE_ONLY|STAGE_ONLY ) query hint to the SELECT statement to change the scope of a single query by a member of the db_owner role.

    • LOCAL_ONLY. Query local data only.
    • REMOTE_ONLY. Query remote data only.
    • STAGE_ONLY. Query only the data in the table where Stretch Database stages rows eligible for migration and retains migrated rows for the specified period after migration. This query hint is the only way to query the staging table.

    Please refer to Manage and troubleshoot Stretch Database and this article for more details.

    Best Regards,

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments