MS SQL ASM - Server Back

Server Back-up Online
Application Support Module
Microsoft SQL Server
Gebruikershandleiding
Inhoudsopgave
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
3 Restore ..................................................................................................................................8
2
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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.
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.
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.
3
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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.
First go to the tab Preferences within the Microsoft SQL workspace of the Backup Manager application:
4
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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.
Note: Each separate SQL Server engine that you wish to include in the backup, requires its
own ASM licence.
After you have registered your SQL Server(s), you can proceed to set the Backup selection, as described in
the next chapter paragraph.
5
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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!
6
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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.
7
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen
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!
8
Back-up Online - Uw bedrijfskritische gegevens veilig opgeslagen