SQL Server supports both implicit and explicit transactions. Implicit transactions are initiated when the system’s session option “Implicit Transactions” is set to ON. In this state, any of a number of commands, including Create, Insert, Update, Delete, Drop, and Select will automatically begin a new transaction. To finalize the transaction, the user must specifically issue... » read more
When two or more users access the same tables at the same time, they’re considered concurrent users. If the operations that they are performing are simple reads, then SQL Server can easily accommodate both users simultaneously. But once one or more users begin updating and inserting records into a table that someone else is reading,... » read more
If your database design calls for the immigration of files such as audio, images or other document types, then you’ll want to investigate the SQL Server feature called Filestream. In the past, immigrating a database with unstructured binary data files meant providing links to their location on the disk drive. This is not an ideal... » read more
Not all of the information neededfor the design of an application has to be storedwithin a single instance of of SQL Server. Through a feature called Linked Servers. SQL Server’s database engine can query tables and other remote instances of SQL Server or even other products. Such as Microsoft Access Databases, Oracle Databases, Excel Spreadsheets... » read more
In the middle of a crisis, where you fear the database is lost, and you need to get it back up and running as quickly as possible, it’s easy to jump right into the Recovery Phase and start restoring the backups that you’ve been carefully creating. But there is an intermediate step that you should... » read more
Database administrators go to great lengths to ensure that their SQL server instances remain secure. They’re usually physically located in a secured room and use appropriate user accounts than authentication protocols to verify permissions during network access. But if your database back-ups are stored off-site or in the cloud, then just as much care needs... » read more
Database snapshots provide a way to view the records in a database as they were at a specific point in time. This read-only copy of the data preserves the state of the information, which can be useful for reporting or auditing since these activities can occur on the snapshot without impacting the source database. Multiple... » read more
SQL Server backup types outline how the data is handled. Here we have several options, but they all start with a full backup. A full backup will maintain all the data files and the transaction log. A full backup is required before any of the other types can be implemented. Once a full backup is... » read more
There’s a balance that needs to be struck between how often the backups occur and how long it takes to recover. These can be summarized by two different objectives that need to be carefully considered when developing your backup strategy. The first is the recovery point objective, or R-P-O. This defines what amount of data... » read more
In the simple model the transaction log automatically truncates itself when the data file has reached certain checkpoints. The benefit is that the log is kept small, but it does so at a cost. Database restores can only occur to the exact point that the last full backup was taken. With a simple recovery model,... » read more