Manage and monitor SQL Server backups from a central location


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:

  1. It is possible to configure Central Management Server, to define server groups, and to add one or more servers to these server groups. This way, T-SQL statements or Policy-Based Management policies could be executed on all registered servers from a Server Group at the same time. The main flaw of CMS setup is complex monitoring and limited automation. Furthermore, only Windows Authentication is allowed when using CMS setups.

  2. You could create multiserver environment. If using this solution, the Master server (MSX), and one or more Target servers have to be configured using SQL Server Agent. In this setup, jobs are initially defined on the master server. The defined jobs are then passed to, and executed on target servers automatically. The reports on the jobs that run on target servers are sent back to the master server, which makes the monitoring much easier.

  3. Use ApexSQL Backup. A simple-to-use software with all needed features in one place. ApexSQL Backup user interface, central repository database and Backup Agent Service are installed on the server that is used for backup management and monitoring. By connecting to other servers using ApexSQL Backup, application automatically installs Backup Agent Service on connected servers. All connected servers are displayed in application server list. Through this application, it is possible to manage and monitor backups with ease, all from a single machine.

Central Management Server and Server Groups

In order to configure this setup, you have to install Central Management Server (CMS) first. Mind that you will not be able to manage databases on this server itself by using central script execution or PBM policies. Therefore, the best practice is to use a machine that does not require database maintenance as a Central Management Server. The entire process consists of few basic steps: creating the CMS, defining new server groups, and registering new servers in these groups. Here are basic instructions for the setup using SSMS:

  1. Log in to a server that you want to use as a CMS

  2. Go to the View menu, and click Registered servers

  3. In Object explorer, connect to all servers that you want to include in this setup. Be sure to use Windows authentication for all of them.

  4. Right click on Central Management Servers, select Register Central Management Server…

  5. In the Server name field, select the server that you want to use as a CMS from the dropdown menu. Optionally, you can replace the name of registered server, or add the description for it. Click on the Test button to make sure that the connection with the server runs properly. If you get the message “The connection was tested successfully”, click the Save button to complete the CMS registration.

  6. To create the server groups, right click on your CMS. Select New Server Group…

  7. Specify the Group name, and Group description (optionally)

  8. By repeating steps 6 and 7, you can add as many groups as you need. IN this article, 3 groups are defined: Production, Sales and Transport

  9. To add new servers to defined server groups, right click on the server group, and select New server registration… From the dropdown menu in Server names, select one of the servers that you want to add to the group. Enter the registered server name and description if needed, Test the connection, and Save changes. By repeating this step, additional servers can be registered and sorted amongst server groups.

  10. All types of queries can be run against any of the defined server groups, including backup queries. By right clicking on a group, and selecting New query, you can define the query that runs against all servers in selected group. In the example, the Backup query is run against all servers in Production group. The results of the query are displayed in the lower screen, in Messages.

  11. Simple PBM policy can be used to monitor the status of backups. Create the policy that you want to evaluate, and run it against single, or all server groups.

Backups in a multiserver environment

To create multiserver environment, you need to assign the master server (MSX), and to add one or more target servers (TSX) to it.

  1. Connect to the server instance that you want to use as a master server
  2. Right click on SQL Server Agent, select Multi Server Administration, and Make this a Master.

  3. The option will start Master server configuration wizard. In first step of the wizard, addresses for the notifications could be set: E-mail, Pager, or Net send address.

  4. Next step assigns the target servers. All registered servers are displayed on the left. Each of them can be used as a target server, simply by adding them to the list on the right window. If you want to add more target servers, that are not yet in the list, you can use Add connection button. Unlike CMS setup, multiserver environment allows SQL Server authentication when adding target servers to the list.

  5. After all servers are added, wizard checks the compatibility between master and target servers. If all processes complete successfully, click Close.

  6. Optionally you can specify new Login to use when connecting to target servers

  7. In final step, the list of pending actions is displayed

  8. The wizard runs the actions from previous step, and If all complete successfully, click close. Should you get an error, you might need to modify startup account for the target server(s), and grant it additional permission, or to make a new account. I also had to change the registry value for MsxencryptChannelOptions from 2 (default) to 0.

  9. After the multiserver environment setup completed successfully, we need to create backup jobs that will run on target servers. To create a new job, connect to the server that is used as MSX, expand SQL Server Agent (MSX), right click Jobs, and select New job.

  10. In New Job window, General tab, the Name, Owner, Category, and Description for the job are specified.

  11. In Steps tab every step of the procedure needs to be defined. To add the first step, click New.

  12. The Step name, Type, and Command need to be defined for the step. For this step, we are using the T-SQL script that backs up all non-system databases in C:\Backup\ folder. When done, click Ok. If additional steps are needed for the job, repeat this step until the job is configured according to your needs.

  13. Schedules, Alerts and Notifications could also be specified for the job
  14. Finally, in Targets tab, select the Target multiple servers radio button, and check the boxes in front of the servers that need to be backed up. Click Ok when done.

  15. To run the created job, expand SQL Server Agent (MSX), and Jobs. Right click on a job that needs to be done, and select Start Job at Step.

  16. If all is done properly, the job will execute all defined steps, and display the Success message

ApexSQL Backup

ApexSQL Backup is 3rd party software application, that can be used to manage multiple backups across any number of servers. It supports both Windows, and SQL Server authentication. The application consists of three main components: User interface, Central Repository Database and an agent service. The user interface is used to manipulate, schedule, and execute jobs; central repository database for storing information and configuration data; and agent service(s) for communication between user interface, central repository database and SQL Server instances. To install ApexSQL Backup, perform the following steps:

  1. The installer could be downloaded here.

  2. Run the installer, select Install ApexSQL Backup, and click Next.

  3. Accept the License agreement and click Next.

  4. Specify the installation path for ApexSQL Backup

  5. Complete the setup by clicking on Close button.

  6. Running ApexSQL Backup for the first time will automatically start the installation of central repository database. In this step, we can choose the server that will host the ApexSQL Backup central repository database. The ApexSQL Backup central repository database can be installed on either local, or network server.

  7. You should also choose Login type for the server that will host the ApexSQL Backup central repository database. You could choose either Windows or SQL Server Authentication, depending on the Login that is used to administer this SQL Server. The Login that is used needs to have administrator privileges on the chosen server. It is also recommended to use SQL Server authentication, if you plan to manage multiple servers that do not belong to the same domain. Click Ok when done.

  8. To add servers that you want to manage with the application, click Add on upper left. You can choose servers which you want to add, and specify the Logins that you use to access these servers. It is best practice to add the server that hosts the ApexSQL Backup central repository database first.

  9. Adding the first server will automatically start the ApexSQL Backup agent service installation. You should specify the windows account that is used to run this Windows service. Make sure that the account that is used has full access to C:\ProgramData\ApexSQL folder, and all of its subfolders. If C: is not your system drive letter, refer to your system drive letter instead. ProgramData is hidden folder by default, so be sure to check “Show hidden files and folders” in folder options, if you want to change permissions on the folder. In the Username field, enter the Windows user account that you use on this machine. In password field, provide the password that you use to log into Windows. A blank password is not accepted. In this example, a domain user account is used.

  10. If you want to add remote servers to the list, just click Add button in the upper left. Choose the icon on the right, and select the server that you want to add from the list. Choose authentication type for the server. The login that is used to access the server needs to have appropriate permissions to backup and restore databases on respective server. It is best to use SQL Server authentication for the servers that are outside of the domain.

  11. Every time you add a server on a new machine, it starts the installation of the ApexSQL Backup agent on that machine. Only one ApexSQL Backup agent per machine is installed, and it manages backups for all servers on that machine. The agent installation is same as in step 9. This way, you can add as many servers to the list as you want.

After the setup is done, we can try to execute some basic operations using the application user interface.

  1. To perform backup operations, click the Backup button on the upper left

  2. This will start the Backup wizard. You can specify the SQL Server that will be used in the operation, exact databases that you want to backup, backup type, and backup component.

  3. In next step of the Wizard, naming and description rules could be specified. One or more destination paths could be defined.

  4. In Options step, additional actions could be performed on the database backups, like verification, compression and encryption.

  5. In the Schedule step, you can decide if you want to execute all defined actions immediately, or you want to create a schedule, and perform the defined actions later. The schedule can be saved and reused, or can be configured to execute automatically.

  6. In final step, all executed actions are listed.

  7. There are several options implemented in the tool that are used for monitoring backups:

    1. You can track all performed backups with Activities option. All activities regarding selected database, server, or complete server list are displayed on the right.

    2. All schedules are displayed in Schedules option. Again, schedules can be displayed for all servers, single server, or a single database. There is also the option to filter schedules, according to actions that are performed in the schedule.

    3. Backup history option shows the details for all backups performed on the selected database.

    April 15, 2016