tracking deadlocks is the easier of the two:
By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.
Write deadlock info to the SQL Server error log:
DBCC TRACEON(-1, 1204, 3605)
Turn it off:
DBCC TRACEOFF(-1, 1204, 3605)
See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on.
https://msdn.microsoft.com/en-us/library/ms178104.aspx
Prevention is more difficult, essentially you have to look out for the following:
Code Block 1 locks resource A, then resource B, in that order.
Code Block 2 locks resource B, then resource A, in that order.
This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.
To prevent this condition, you can do something like the following
Code Block A (psuedo code)
Lock Shared Resource Z
Lock Resource A
Lock Resource B
Unlock Shared Resource Z
...
Code Block B (pseudo code)
Lock Shared Resource Z
Lock Resource B
Lock Resource A
Unlock Shared Resource Z
...
not forgetting to unlock A and B when done with them
this would prevent the deadlocking between code block A and code block B
From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.
Mirroring is a technology that increases the high availability for user databases and creates a copy of the source db that is maintained in real time. This is done by delivering all the active transaction log records to the mirror server.
From MSDN:
"Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise. "
Clustering is a technology that provides high availability for the entire SQL Server instance. This means that it's possible to have two servers with identical structure (OS, applications, services, service packs, patches...etc) and with shared storage (the data itself).
From MSDN:
"SQL Server failover clustering provides high-availability support for an entire SQL Server instance. SQL Server failover clusters are built on top of Windows Server failover clusters. To create a SQL Server failover cluster, you need to first create the underlying Windows Server failover cluster."
Here's a course from the Microsoft Virtual Academy about High Availability as offered today by MS: Understanding Microsoft’s High-Availability Solutions.
Best Answer
The native backup of sql server, commonly have extension of
.bak
is something which is proprietary of microsoft.Different RDBMS providers have different formats and it also depends on whether they are open source (PostgreSQL and MySQL) or are patented (MS SQL Server, ORACLE or SybaseASE).
For MS SQL Server :
A backup is a page-by-page copy of the database, as it existed the moment you took the backup, a restored copy of that database will be in exactly the same state as it was the moment you took the backup.
A backup contains more than just the data. It contains the entire structure of the database including all the data structures and data along with procedures, views, functions and any other code. It also contains the settings and definitions of the database along with the users of the database. Exception is
contained databases
have a concept calledUSER WITH PASSWORD
.If you want to use something that you mentioned, have a look at SqlDump - Microsoft SQL Server database backup program
Note: I have not tried the SQLDump, as I would personally prefer SSIS or T-SQL along with BCP and BULK INSERT - to move data out and in of sql server.