1.Difference between Checkpoint and Lazy Writer
S.No CheckPoint Lazy Writer 1 Flush dirty pages to Disk Flush dirty pages to disk 2 Flush only Data pages to disk Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects) 3 Default, Occurs approximately every 1 minute Occurs depending upon memory pressure and resource availability 4 Can be managed with sp_confige -recovery interval option It is lazy, Sql server manages by its own. 5 Does not check the memory pressure Monitor the memory pressure and try maintain the available free memory. 6 Crash recovery process will be fast to read log as data file is updated. No role in recovery 7 Occurs for any DDL statement Occurs per requirement 8 Occurs before Backup/Detach command Occurs per requirement 9 Depends upon the configuration setting, we can control. Works on Least recent used pages and removed unused plans first, no user control. 10 For simple recovery it flush the tlog file after 70% full. No effect on recovery model. 11 Can manually /Forcefully run command “Checkpoint” No command for Lazy Writer 12 Very Less performance impact No performance impact
2.Difference between Mirroring and Log Shipping
S.No Mirroring Log Shipping 1 Principle can have single mirror Multiple stand by servers can be possible. 2 Generally good to have 10 DB’s for one server No limit 3 No data loss and can be used as high availability like Clustering May be some data loss as per schedule. And secondary server takes some manual work and time to be primary 4 Read log read and transfer the committed transaction through endpoints. Transfer the log back up and restored at standby server. 5 Only committed transaction Committed as well as uncommitted and whole log backup restores. 6 PAGE repair is possible if principle database page gets corrupt N/A 7 Mirrored DB can only be accessed using snapshot DB Secondary server can be reporting server (read-only) 8 Principle and Mirror server should have same edition Primary and secondary server should be compatible server for restore. 9 Require FULL recovery model Require FULL or Bulk-Logged recovery model 10 Requires Sql Server 2005 SP1 or higher – Enterprise or Developer Editions Enterprise edition for Sql Server 2000 and even Standard edition for 2005 can works 11 Immediate data moved depending on SEND and WAIT queue Can control the flow of data by scheduling jobs 12 As Immediate data moves, user error reflects at mirrored DB As delay in data transfer can avoided user error.
S.No Change Track Change Data Capture 1 It is about fact: It captures only the fact as the tracking table has changed. It does NOT capture the data.
Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured
It is about the Data: Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. 2 Storage:
Internal tables are placed on the same filegroup as the parent entity. You could use the sys.internal_tables catalog view to show all the internal tables and parent entities. For example: select name, object_name(parent_id) as parent_object from sys.internal_tables
When change data capture is enabled for a database, a few things are added to the database, including a new schema (called cdc), some metadata tables, and a trigger to capture Data Definition Language (DDL) events.
The two function names are, respectively, fn_cdc_get_all_changes_ and fn_cdc_get_net_changes_, with the capture instance name appended. Note that (like the change tracking feature) this functionality requires the table to have a primary key or other unique index.
3 Supported on “Simple” recovery model also.
It is recommended that you use snapshot isolation when change tracking is enabled. Snapshot isolation itself can add significant workload overhead and requires much more careful management of tempdb.
Prevents Log truncation.
Forces full logging of some bulk operations.
One major point to note here is that once change data capture is enabled, the transaction log behaves just as it does with transactional replication—the log cannot be truncated until the log reader has processed it. This means a checkpoint operation, even in SIMPLE recovery mode, will not truncate the log unless it has already been processed by the log reader.
4 It uses synchronous tracking mechanism.
once a database is enabled for change tracking, a version number is instituted, which allows ordering of operations
Change Data Capture (CDC) uses the asynchronous process that reads the transaction log. 5 Change Tracking has minimal impact on the system. It has almost nil impact as it asynchronous mechanism reads from the transaction log. 6 It uses TempDB heavily It uses transaction log. 7 DDL Restriction:
There are restrictions on the DDL that can be performed on a table being tracked. The most notable restriction is that the primary key cannot be altered in any way. The other restriction worth calling out here is that an ALTER TABLE SWITCH will fail if either table involved has change tracking enabled.
No such DDL restriction 8 SQL Agent not needed t requires SQL Agent to be running.
SQL Agent Job & Transaction Replication:
Two SQL Agent jobs may be created: the capture job and the cleanup job. I say "may be created" because the capture job is the same as the one used for harvesting transactions in transactional replication.
If transactional replication is already configured, then only the cleanup job will be created and the existing log reader job will also be used as the capture job
9 Permission required to enable: SYSADMIN Permission required to enable: DBOwner
S.No Index Rebuild Index Reorganize 1 Index Rebuild drops the existing Index and Recreates the index from scratch. Index Reorganize physically reorganizes the leaf nodes of the index. 2 Rebuild the Index when an index is over 30% fragmented. Reorganize the Index when an index is between 10% and 30% fragmented. 3 Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions. Always prefer to do Reorganize the Index. 4 T-SQL for Rebuilding all Indexes of a particular table.
ALTER INDEX ALL ON HumanResources.Employee REBUILD
T-SQL for Reorganize all Indexes of a particular table.
ALTER INDEX ALL ON HumanResources.Employee REORGANIZE
S.No User-defined SP System-defined SP 1 Once we create User defined SP in one database i.e available to only that database directly.i.e
we cannot call it from some other DB’s directly
System defined sp are available in master DB.These sp’s can be directly called from any DB 2 UDSP will be used to fulfill the user requirements SDSP will be used for managing sql server