Tables in the SQL Server “master” database, will they cause problems

sql-server-2008-r2

Folks, please be kind on me… I'm just an 'accidental' DBA due to our DBA resigned, so I'm totally a newbie in DBA…

You see, I have this application, "ESET Remote Administration Server" (ERAS) that stores its logs and analysis on (originally) a local Access database.

The decision was to migrate its database to a SQL Server 2008 R2 machine.

ESET (the maker of the software) helpfully provided tools to perform such migration; unfortunately, being the DBA neophyte that I am, I didn't realize that I have to first create my own database (on the SQL Server side) and assign that database as the 'default' database for ERAS' ODBC connection.

Now, the migration tool had successfully created a whole bunch of tables inside the "master" database.

My questions:

Should I leave things be as it is, or should I re-migrate the ERAS database to a different database?

If you suggest me perform a re-migration, my plan is to (1) create a new instance, (2) create a new database within the new instance, (3) create a new ODBC System DSN on the ERAS server pointing to the new DB in step 2, (4) use ESET's migration tool to migrate from the current DSN to the new DSN.

Do you think I missed a step there?

Thanks beforehand for any guidance.

Best Answer

I would recommend doing the move to a dedicated database. Here are a couple of reasons why:

  • If you ever want to migrate this database to a different instance of SQL Server you'll need to do this anyway. You can't migrate master.
  • The master database is not meant for user data. It's meant to be the central point for data and code needed to keep the instance functional. Adding things in master muddies the waters there.
  • Adding things to master requires changing security in master unnecessarily.

These might not be huge issues for you in your situation but I always advocate best practices unless there is a solid business case to do otherwise.

Regarding your re-migration plan, I don't see that you need to create a new instance of SQL Server. You should be fine with just creating a new database for ERAS and redoing the migration. Then you can purge master of the objects that ERAS created in it. In your ODBC connection you specify what database the connection is to use so you should be able to change it just fine to point to the new database.

So, here are the steps I would suggest:

  • Create your new database (call it ERAS or whatever)
  • Create a new SQL Login (at the server level) if you have not already done so
  • Add the new SQL Login as a user in your new database (most likely as a member of the dbo role)
  • Change your ODBC connection to point to the new database (and to use the new login credentials you just created, if necessary)
  • Perform the migration steps, which should now create all the required schema objects and data in your new database
  • Clean up master. Here you're best off manually removing the ERAS objects instead of restoring from a backup. Restoring master is not a trivial task and if something goes wrong it can render your entire SQL Server instance unusable
  • Profit!
Related Topic