Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 73 of 212

62 C H A P T E R 4 | Improved database engine you can reach your SQL Database over port 1433, which is a commonly blocked outbound port on many networks. Another monitoring tool at your disposal is the new Remote Query operator in the execution plan for a stretch table, as shown in Figure 4-11. SQL Server 2016 also includes the Concatenation operator to merge the results of the on-premises data with the remote query results. Figure 4-11: Reviewing the execution plan for a stretch table. An important design pattern with Stretch Database is to ensure that your queries do not regularly retrieve unnecessary rows. Running poorly written queries against a stretch table can apply adverse performance. When troubleshooting performance issues on stretched tables, start your tuning effort as you would on a regular on-premises database. After eliminating issues related to your on-premises instance, examine the Azure portal to understand how the workload affects the stretch database. If your remote query performance is still not sufficient, you have several options for tuning. First, ensure that your remote database is in the Azure data center nearest your on-premises data center to reduce latency. Next, monitor the Azure portal to observe the performance characteristics of the underlying Azure database. You might need to increase the service tier of the SQL Stretch Database. Last, work with your network administrator to guarantee quality of service between your site and your remote database. Backup and recovery with Stretch Database Backup and recovery of a stretch-enabled database does not include the SQL Stretch Database containing your remote tables. Nonetheless, your data remains protected because SQL Stretch Database leverages the built-in backup features of SQL Database. Accordingly, SQL Database is constantly making full and transaction log backups. The retention period for these backups is determined by the service tier of the database. However, when you back up your on-premises database, you are taking a shallow backup. In other words, your backup contains only the data that remains on-premises and does not include the migrated data. To restore a database, follow these steps: 1. Restore your on-premises SQL Server database. 2. Create a master key for the stretch-enabled database. 3. Create a database-scoped credential for your SQL Database. 4. Run the restore procedure.

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016