Server Back-up Online
Application Support Module
Microsoft SQL Server
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
2
I n h o u d s o p g a v e
1 Product Overview...3
1.1 System Requirements...3
1.2 Hot Backup...3
2 Installation and Configuration
...4
2.1 Configuration ...4
2.2 Backup selection...6
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
3
1
Product Overview
Microsoft SQL Server is a business application platform that typically runs in a 24/7 uninterrupted mode. Although KPN Backup Manager V4 does support backup of open files, this method is not recommended for the large database files of this type of systems.
For this purpose special plug-in modules have been developed, the so-called Application Support Modules. Although it is technically possible to make offline backups by shutting down the SQL Server engine and make a backup of their files, this method is time consuming, inefficient and not desirable from an operations point of view.
The KPN Backup Manager V4 Application Support Module for MS SQL Server delivers a facility to backup and restore MS SQL databases while the engine is running, so-called ‘hot backups’. The solution leverages the internal Backup and Recovery mechanisms available within SQL Server, thereby respecting the integrity of the database structures.
1.1 System Requirements
The KPN Backup Manager Application Support Module (ASM) for Microsoft SQL Server runs as a plug-in module within the KPN Backup Manager V4 main applications. The availability of the SQL Server
functionality is dependent on the licence code of you Backup Manager installation. The plug-in module supports the following platforms:
Microsoft SQL Server 7 Microsoft SQL Server 2000 Microsoft SQL Server 2005
The software does not require any special hardware or software options installed, it runs on any system that complies with the general requirements of the KPN Backup Manager V4. It is not necessary to install the software on the SQL Server machine itself, as the software has the capability to backup “remote” SQL Servers.
1.2 Hot Backup
With the Application Support Module (ASM) for MS SQL Server, customers benefit from the “hot” backup features supported by their database engine.
The KPN Backup Manager leverages the internal backup facilities provided in MS SQL Server to the maximum, but fully integrates them into its user friendly interface, together with the file backup facilities and eventual MS Exchange functionality, thus ensuring that backup schedules are in sync and running fully automatically.
Note: As the software first performs a “dump” of the databases that need to be backed up, to the Windows temp directory, it is important that you keep sufficient disk space available to dump all your databases.
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
4
2 Installation and Configuration
The basic software installation for the MS SQL Server ASM is done in parallel with the KPN Backup Manager software installation. If your licence code includes support for MS SQL, the necessary software options are installed with the rest of the Backup Manager software.
If you decide to later upgrade your KPN Backup Manager software installation with the MS SQL ASM functionality, a new client licence needs to be processed on your backup server.
Also the licence code, user id and password on your client installation will change. The necessary software options will become available the next time you start your KPN Backup Manager client.
Figure 1 Backup Manager with MS SQL plugin installed
2.1 Configuration
After you have successfully installed (or upgraded) KPN Backup Manager with the MS SQL Server ASM, you need to configure the software such that it recognises your MS SQL database server(s) and knows which databases to backup, and which not to backup.
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
5
Figure 2 Microsoft SQL Workspace - Preferences tab
Here you can register any SQL Server that you wish to include in the backup, including their login credentials.
With regard to login, you can use Windows authentication, provided that the user under which the KPN Backup Manager application is running, does have sufficient access rights to your SQL Server(s). The software is able to address a local SQL Server engine, running on the same machine as the Backup Manager application, but also remote SQL Server systems can be addressed, using their Computer Name or network address.
After you have registered your SQL Server(s), you can proceed to set the Backup selection, as described in the next chapter paragraph.
Note: Each separate SQL Server engine that you wish to include in the backup, requires its own ASM licence.
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
6
2.2 Backup selection
Setting the backup selection for your SQL Server(s) is very similar to selecting files and folders for the backup, as described in the Backup Manager user manual.
Figure 3 SQL Server Backup Selection
On the Backup tab in the Microsoft SQL Workspace of the Backup Manager application, a tree-like representation of your database server(s) and the underlying databases appears.
Parallel to the file backup feature, you can basically set two types of selection: An exclusive selection, indicated by a gray colour in the selection box.
An inclusive selection, indicated by a white selection box.
In an exclusive selection, underlying databases are not included in the selection, unless explicitly selected. You can create an exclusive selection by unfolding the tree under the target SQL Server by clicking the plus (“+”) sign and just select the database(s) you want to be backed up.
Note: Be aware that in an exclusive selection, new databases that you create after configuration of the Backup Manager software, are not automatically added to the backup selection. You need to manually change your selection if you wish these databases to be included!
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
7
In an inclusive selection, you select at the database server level and all underlying databases are
automatically selected for backup, unless they are explicitly excluded from the selection by un-checking their checkbox. In the above example, you would check the “(local)” checkbox, after which all underlying
databases are checked as well. If you do not wish for example to backup the ”pubs” database, you would have to explicitly uncheck the respective checkbox.
The advantage of an inclusive selection is that you can never accidentally forget to include any new databases that are created after installation and configuration of the Backup Manager software. The downside might be that you create many temporary or development databases and waste more space on your backup server than absolutely necessary.
However the delta blocking mechanism, used for data reduction, also works on the SQL Server databases, to the effect that highly similar databases can share several data blocks, reducing the net disk space required as well as the upload time to the backup server.
After creation of the SQL Server selections, your selected databases will become part of the daily backup routine.
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
8
3
Restore
The restore user interface is very similar to the backup screen we have seen in the previous chapter:
Figure 4 Microsoft SQL Restore screen
You can select the backup session from which you want to restore a database in the “Backup time” drop down box in the right upper corner of the workspace. A list of available databases – in this case only the “Northwind” database – will be displayed.
In the bottom part of the screen you can set a name for the database to be restored to.
Note: If you keep the original name of the database, the actual version of that database will be overwritten!
