A recovery model is a database configuration option that determines the type of backup that one could perform, and provides the ability to restore the data or recover it from a failure.
The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.
Read more »
This is the second article in the “Backup and Restore (or Recovery) in SQL Server” stairway series (see the full TOC below). This article deals with the different phases of data management life cycle and it encompasses the following topics:
In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.
Read more »
We manage data in a growing environment where our clients query some of our data, and on occasion will query past data. We do not have an environment that scales and we know that we need to archive some of our data in a way that allows clients to access it, but also doesn’t interfere with current data clients are more interested in querying. With the current data in our environment and new data sets will be using in the future, what are some ways we can archive and scale our environment?
Read more »
Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA.
In this article, I will be discussing every option available in full backup screen of SQL Server 2016.
Read more »
Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.
Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.
Read more »
Following best practices, we should apply principles like segregation of duties. This implies the segregation of application environments. In particular, we might see for a given application that it incorporates at least two environments: a production environment and a non-production environment that exists for testing, Q/A, training, etc
Read more »
Database availability is critical to every enterprise and conversely, unavailability directly can create a severe negative impact to the business in today’s world. As database administrators, it is important that we ensure we take all possible steps to minimize data loss. While it is naïve to think that our databases are invincible because of all such precautions, we can always aim to bring the databases back into operation as quickly as possible by meeting the RPO and RTO. This way, the business is unaffected, and we also meet our SLAs.
Read more »
The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.
Read more »
In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux.
Read more »
Terabytes of data, millions of rows; the entire business depends on this — a database administrator’s responsibility is as massive as it sounds. Apart from keeping the data secure, a database administrator also has to keep the system up and running, and restore the data as needed, in case of a failure, with minimal impact to the business.
Read more »
Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”.
Read more »
After receiving new additions to backup and restore capabilities of SQL Servers like file and page restores, I thought that nothing will surprise me anymore, but well, here it is; SQL Server file-snapshot backups in Azure.
Read more »
The Stretch database is one of the latest and most-exciting features that has been released with SQL Server 2016. In the previous article, we examined the concept behind it, the steps to implement it, monitor it and how it can be disabled. As a natural follow-up, we will explore the backup and restore topic when it comes to those kind of databases.
Read more »
Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.
Read more »
Long before I turned to Data Warehousing and OLTP replica environments such as Operational Data Stores as a form of data redundancy strategies, I had been using database snapshots. During that time, the analytics and reporting teams were directed towards a database snapshot as a data source for their development. Another benefit of having a database snapshot, is the ease of knowing that should unintended changes be committed in source, you could easily revert the operation by restoring source database off the snapshot.
Read more »
Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.
Read more »
Backup and recovery are some of the most important DBA tasks, although they look simple enough and usually you setup them, leave them running on schedule and only come back if they fail – there is a whole new world in regards to optimization you can do to make them faster, better and … smaller.
But why bother? Considering that the modern databases grow at such fast pace you may face a situation where you are not able to fit in your maintenance windows or service contract obligations. Let us take a look on the three main areas where we can work on:
Read more »
The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted.
Read more »
If you are a DBA and you have one single server with SQL Server to administer, backup the database is a simple task. However, there are several companies with hundreds and some with thousands of Servers with SQL Server. To handle multiple SQL Server backups at the same time is a nightmare.
Read more »
This article is a continuation of a guide where we are checking all the available backup preferences for a database contained in an Availability Group.
Read more »
Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.
Read more »
With the evolution of the Cloud, specifically talking about Azure, SQL Server team started to take advantage of the new opened horizon. We already discussed pros and cons of send backups to Azure, now let’s check how to do this!
Following the “Hybrid Scenarios series”, we already discussed about the connectivity options, and pros and cons of store our backup files in Azure.
In this article, we will specifically talk about the two options that we currently have, involving Azure: Blob Storage and Managed Backups.
Read more »
With the evolution of the Cloud, specifically talking about Azure, SQL Server team started to take advantage of the new opened horizon. In this article we will discuss about backups. What are the pros and cons of store your backup files in Azure?
Read more »
Sometimes we need to move our local files, SQL scripts, backups from our local machine to Azure or vice versa. This can be done manually by accessing to Azure and using a browser, but there are other methods to automatically do this. This article describes the Microsoft Azure Storage Tool. This is a command line tool used to upload data to Azure from a local machine or to download data from Azure to our local machine. The article will describe step by step how to work with this tool.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy