One of the main tasks for every database administrator is creating a reliable disaster recovery plan. The plan always includes multiple backup and restore operations. Usually, opting for conventional, single-file backups should suffice, but in some cases, resources like disk space, backup time, or both could be the issue. This is usually the case when working with large databases.
One of the most important tasks for any database administrator is to create a foolproof disaster recovery plan. This plan usually includes multiple backup and restore operations. Most of the time, opting for conventional backups should suffice, but in some cases, storing all backups on a single backup device may prove to be a bad idea. As the databases grow with time, the backups become larger, and backup devices less stable due to frequent read/write operations. If the backup device fails, all of the backed up data might be lost. To avoid this scenario, some administrators take multiple copies of their backup files, and store them on different backup devices. There are a few ways to do this:
SQL snippets can be a big boost to productivity when writing T-SQL. First, to use a SQL snippet in a script, there is no need to know the syntax, only the purpose of that SQL snippet (e.g. delete an object, create a table, etc.). Using snippets also reduces of the lines of code that have to be typed, and thus decreases the potential for errors that could occur from typing.
One of the challenges these days is how to pull the latest changes (of SQL objects) from the source control repository and deploy them into a SQL database. This process is particularly helpful in the CI workflow Build step, when developers want to build a SQL database from the committed changes in the source control repository, so they can test if their changes compromised SQL objects from the source control repository or they will be built successfully.
In this article, the second Build step of the CI workflow will be described. The Build step is a step in which a database is built using the latest changes in the source control repository and once the build process is finished, a feedback of success/failure is provided to developers.
More often than not, a top level requirement for all SQL Server DBAs is to have mechanisms to capture who changed what and when and to have stored audit information available for further investigation. In order to achieve this, database auditing needs to be set up using various mechanisms. One of those mechanisms is a trigger-based auditing.
This article will be about how to include Data model diagrams in the documentation generated by ApexSQL Doc. With this feature, it’s possible to see graphically display database tables and their relationships.
Having a SQL database being version controlled locally, by storing all changes in a repository on a local machine can be quite handy. In the context of team based database development, it is necessary to establish the environment where changes can be tested locally, specific revisions reverted from the commit history, and doing such things before pushing changes to the remote repository where the rest of the team will be able to review them, and apply against a local database copy.
Performing regular database back-ups is a crucial part of any disaster recovery plan. There are many aspects that should be taken into consideration when planning long term backup strategies. It is necessary to choose the backup type and backup location and to plan how frequently to perform the backups. Additionally, the processes of verification or encryption could also be included in the plan.
Nowadays, it’s quite common that database changes are made several times a day and that a number of developers are making changes against the same database. Due to these often changes, a certain “process” has to be followed in order to avoid any potential problems in the later stages of development (Quality Assurance (QA), Staging, User Acceptance Test (UAT)) and at the end in production. When talking about the Continuous integration (CI) for SQL databases today, it’s often referred to a process of several steps:
The essence of each source control system is the ability to easily review the history of committed revisions. In addition, to comparing revisions, a user needs to get a specific revision and apply it against a database. Each revision should have a unique timestamp and should carry the information about the user who did the commit. Such system ensures that it is easy to determine who committed what and when, and in some way provide a complete auditing trail of committed revisions.
Running and maintaining multiple SQL Server instances can often be a formidable challenge, especially if these instances run on multiple servers. It is easy enough to set up a SQL Server agent job for each server to automate the backups, but what happens if there are 20, 30, or 100 servers that need maintenance? In this scenario, configuring agents on each server would take forever, and monitoring the entire setup would prove to be a nightmare for any administrator. Of course, there are several solutions for this scenario:
Having a good recovery plan that ensures database backups are being taken on a regular basis and safely stored on specific or multiple storage drives greatly increases database safety and recovery options when a disaster or accident occurs. Nevertheless, creating regular full database backups doesn’t provide full point-in-time recovery, since restoring the latest full backup will restore a database to a proper state at the time a backup was created, but all changes, both schema and data, which have occurred after the last full backup will be practically lost.
SSAS is a powerful and popular feature in the SQL Server ecosystem. That’s why ApexSQL Doc has implemented support for automating SSAS cube documentation so you can have full documentation of your database in no time. We have created this article so you can see how easy it is to create an automated process which will generate documentation with ApexSQL Doc.
Wouldn’t it be a neat trick if you could document a database with a double click? With this article, we’ll demonstrate a few simple steps to allow you to make a script file which will run a process that generates documentation for your database. After that you can just execute it to repeat the process with just two mouse clicks or even set up a job to run it automatically every night. In this article you will learn how to create this automated process of database documentation by using ApexSQL Doc.
Imagine a scenario where there is a need to get a database into source control quickly and easily, including all schema objects and data from certain code tables that won’t change aka Static data. Then, once a database is ported successfully to source control, to be able to update the repository nightly with any and all changed objects. In this way, a database will be fully and automatically source-controlled, without having to worry about direct integration, check-ins, check-outs, etc., essentially providing much of the “gain” of database source control integration, with little of the “pain”. This article will describe how to build this “poor man’s” SQL database source control integration system using a 3rd party tool, ApexSQL Script.
Recovering work after SQL Server Management Studio crashes
Imagine a situation of working in SSMS, and suddenly the power goes out, the system gets crashed, SSMS gets crashed or the work session was abruptly terminated for any other reason, which causes the loss of unsaved work on various SQL scripts. In this situation SSMS provide prompt window for recovered data after restarting:
In this article we will explain how to create a simple batch file to automatically synchronize database schema changes with a source control repository from any individual objectchanges detected in the database.