This article is half-done without your Comment! *** Please share your thoughts via Comment ***
How to change the database owner of the database?
|
1 |
EXEC sp_changedbowner 'MyAdmin'; |
What is Singleton Lookup?
A singleton lookup is a seek operation whereby a single record/page of data is retrieved. This can occur, for example when you perform a query that needs to access only a single record, or a few records from a single page.
What is a Range Scan?
A range-scan is a scan operation whereby a range of pages are retrieved – think of this as a full-scan with boundaries.
What is Checkpoints?
A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.
What are the different type of Checkpoints?
- automatic – Issued automatically in the background.
- indirect – Issued in the background to meet a user-specified target recovery time for a given database.
- manual – Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection.
- internal – Issued by various server operations such as backup and database-snapshot creation to guarantee that disk images match the current state of the log.
What is Database Snapshot?
A database snapshot is a view of what the source database looked like at the time at which the snapshot was created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the data will be exactly as it was then.
To use database snapshots to recover from an unwanted DML statement, you need to have a suitable snapshot in place. Snapshots can only be created by using a T-SQL statement.
What is Sparse file in Database Snapshot?
To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data.
As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.
Explain Sparse file Size in database snapshot.
At the creation time sparse file will take very little space, but as the data changes occurred into the parent database, data page before the changes copied into the sparse file.
Thus sparse file grows. Sparse files are the feature of NTFS file system. As the sparse file grows NTFS will allocate the space to sparse file gradually.
How to create database snapshot?
Based on the current size of the source database, ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.
Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database.
How does a snapshot work?
- When you create a snapshot a sparse file is created for each data file
- When data is modified in the source database for the first time, the old value of the modified data is copied to the sparse file
- If the same data is the subsequently changed again, those changes will be ignored and not copied to the snapshot
- When you query the snapshot, it first checks if the data is available in the snapshot. If it’s there it reads if from the snapshot. If it’s not there, I reads through to the source database and gets the data from there instead, because that means the data has not yet changed since the time the snapshot was taken.
Can we change the permissions in a database snapshot?
No it’s not possible.
Can we take a backup of a database snapshot?
No.
What are the benefits of Database Snapshots?
- Snapshots can be used for reporting purposes.
- Using a mirror database that you are maintaining for availability purposes to offload reporting.
- Safeguarding data against administrative and user error.
What is hot standby and warm standby server?
Warm servers are often used for replication and mirroring.
A hot server is a backup server that receives regular updates and is standing by ready (on hot standby) to take over immediately in the event of a failover.
What is copy-on-write operation in database snapshot?
Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot.
This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created.
Is database snapshot transitionally consistent?
Each database snapshot is transitionally consistent with the source database at the moment of the snapshot’s creation. When we create a database snapshot, the source database will typically have number open transactions.
Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transitionally consistent. Just like it follows the recovery interval step and it will not affect the source database.
Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.
