In this article, we discuss Metadata stored outside the user database and while shifting server instances need to make available in the destination server in a correct fashion.
When you are working on SQL Server and need to shift your existing database to another instance you will not just need the objects inside the database, but also the ones that are present outside the database. There is only a limited amount of data a database can store, sometimes certain key objects also fall outside the scope of one single database, and continue to form the objects on which the database still has dependencies. All these objects which do not form a part of the database but are crucial should be available on destination server instance. This can include logins of an application that would be stored in the form of metadata in Master database; all of these are the objects that need to be recreated on destination server when shifting the database instance.
If the maintenance plan of the database or application is dependent on SQL Server Agent jobs, which have the metadata stored in msdb database, then those jobs also need to be recreated on the server instance. Much like this, the metadata for the server level trigger will also be found in master.
Key points to keep in mind when shifting database to another instance.
The metadata for all the dependent objects and entities in master and msdb needs to be recreated over destination server instance. For instance, if your application makes use of server lever triggers then simply attaching or even restoring the database over another system will not be enough. You will have to recreate the metadata manually for all the triggers in the master database to ensure that the database works as expected. There are several complications that might occur while you are in the process of shifting databases, to know what they may be and how to deal with them, read ahead.
1. Dealing with issues arising during shifting databases and recreating objects
Apart from recreating the multiple objects that you might, you will also have to deal with several other issues, a few of which are listed below
2. Server Configuration Settings
In the 2005 and later editions of SQL Server, the key features and services are selectively installed and started. This is helpful in reducing the surface area exposed to attacks. In case of default configuration for many new installations, many features are disabled. If the database is relying on any of these disabled features or services, then this will also have to be enabled on destination server instance.
This refers to a record that is used for containing the authentication information which is useful for connecting to any resource that is present outside SQL Server.
4. Database Ownership
If the database is restored on a different computer, the Windows user or the SQL Server login, the person that initiated the restore process will now become the owner of the newly created database. At the time of database restore, the system administrator or the newly formed owner of the database can opt for changing the ownership of the database.
To avoid any chances of data loss during a database crash, companies need to look beyond their standard backup plans and invest in a specialized tool that can repair SQL Server database files.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com