When you enable Stretch Database for an on-premise SQL Server 2017 database, SQL Server automatically creates a new stretch database in MS Azure SQL Database as an external source and a remote endpoint for the database.

When you query the database, the SQL Server's database engine runs the query against the local or remote database, depending on the data location. Queries against stretch-enabled tables return both local and remote data by default. This is completely transparent to the database user. This means that you can use Stretch Database without changing Transact-SQL code in your queries, procedures, or applications. You can stretch an entire table or a portion of table data. The data migration is done asynchronously and transparently. In addition, Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. The following figure illustrates the Stretch Database architecture:

Data can be located in three stages:

• Local data: Local data is data in the table that remains in the on-premise instance. This is usually frequently used or hot data.
• Staging (eligible data): Eligible data is data marked for migration but not migrated yet.
• Remote data: Remote data is data that has already been migrated. This data resides in Microsoft Azure SQL Database and is rarely used.

Stretch Database does not support stretching to another SQL Server instance. You can stretch a SQL Server database only to Azure SQL Database.