Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari iniPelayar ini tidak lagi disokong.
Naik taraf kepada Microsoft Edge untuk memanfaatkan ciri, kemas kini keselamatan dan sokongan teknikal yang terkini.
Applies to:
SQL Server
You can take advantage of a mirror database that you are maintaining for availability purposes to offload reporting. To use a mirror database for reporting, you can create a database snapshot on the mirror database and direct client connection requests to the most recent snapshot. A database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot's creation. To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.
Unlike the mirror database itself, a database snapshot is accessible to clients. As long as the mirror server is communicating with the principal server, you can direct reporting clients to connect to a snapshot. Note that because a database snapshot is static, new data is not available. To make relatively recent data available to your users, you must create a new database snapshot periodically and have applications direct incoming client connections to the newest snapshot.
A new database snapshot is almost empty, but it grows over time as more and more database pages are updated for the first time. Because every snapshot on a database grows incrementally in this way, each database snapshot consumes as much resources as a normal database. Depending on the configurations of the mirror server and principal server, having an excessive number of database snapshots on a mirror database might decrease performance on the principal database. Therefore, we recommend that you keep only a few relatively recent snapshots on your mirror databases. Typically, after you create a replacement snapshot, you should redirect incoming queries to the new snapshot and drop the earlier snapshot after any current queries complete.
Nota
For more information about database snapshots, see Database Snapshots (SQL Server).
If role switching occurs, the database and its snapshots are restarted, temporarily disconnecting users. Afterwards, the database snapshots remain on the server instance where they were created, which has become the new principal database. Users can continue to use the snapshots after the failover. However, this places an additional load on the new principal server. If performance is a concern in your environment, we recommend that you create a snapshot on the new mirror database when it becomes available, redirect clients to the new snapshot, and drop all of the database snapshots from the former mirror database.
Nota
For a dedicated reporting solution that scales out well, consider replication. For more information, see SQL Server Replication.
This example creates snapshots on a mirrored database.
Assume that the database of a database mirroring session is AdventureWorks2022
. This example creates three database snapshots on the mirror copy of the AdventureWorks
database, which resides on the F
drive. The snapshots are named AdventureWorks_0600
, AdventureWorks_1200
, and AdventureWorks_1800
to identify their approximate creation times.
Create the first database snapshot on the mirror of AdventureWorks2022
.
CREATE DATABASE AdventureWorks_0600
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_0600.SNP')
AS SNAPSHOT OF AdventureWorks2022
Create the second database snapshot on the mirror of AdventureWorks2022
. Users who are still using AdventureWorks_0600
can continue to use it.
CREATE DATABASE AdventureWorks_1200
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_1200.SNP')
AS SNAPSHOT OF AdventureWorks2022
At this point, new client connections can be programmatically directed to the latest snapshot.
Create the third snapshot on the mirror AdventureWorks2022
. Users who are still using AdventureWorks_0600
or AdventureWorks_1200
can continue to use them.
CREATE DATABASE AdventureWorks_1800
ON (NAME = 'datafile', FILENAME = 'F:\AdventureWorks_1800.SNP')
AS SNAPSHOT OF AdventureWorks2022
At this point, new client connections can be programmatically directed to the latest snapshot.
Database Snapshots (SQL Server)
Connect Clients to a Database Mirroring Session (SQL Server)
Peristiwa
31 Mac, 11 PTG - 2 Apr, 11 PTG
Acara pembelajaran SQL, Fabric dan Power BI terbesar. 31 Mac - 2 April. Gunakan kod FABINSIDER untuk menjimatkan $400.
Daftar hari iniLatihan
Modul
Get started with SQL Database in Microsoft Fabric - Training
Learn how SQL Database in Microsoft Fabric works, the key concepts, and practical examples to help users SQL Database effectively as part of their analytics solutions.
Pensijilan
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Dokumentasi
Database Snapshots (SQL Server) - SQL Server
Find out how to use database snapshots to create read-only, static views of a database in SQL Server. See their benefits, prerequisites, and limitations.
Create a database snapshot (Transact-SQL) - SQL Server
Find out how to create a SQL Server database snapshot by using Transact-SQL. Learn about prerequisites and best practices for creating snapshots.
View a Database Snapshot (SQL Server) - SQL Server
Learn how to view a SQL Server database snapshot using SQL Server Management Studio or Transact-SQL.