3

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.

Improve this question
2
  • 1
    Skip step 1. I don't see any reason to create a new SQL Server instance for this database. Start with step 2 and create a new database, then perform another migration, then cleanup the master database.
    – joeqwerty
    Jun 28, 2013 at 14:57
  • 1
    Like I wrote in my comment to @squillman below, I am not yet comfortable with poking in the master database (to perform cleanup). I think I'll just create a new instance. Thanks anyways!
    – pepoluan
    Jun 28, 2013 at 17:17

1 Answer 1

Reset to default
3

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!
Improve this answer
2
  • Well, since I'm a 'greenhorn' DBA, I totally am not comfortable (yet) with 'cleaning the master database', so I will chicken out and create a new instance anyway (and, after successful migration, delete the instance with the tainted master). Anyways, thank you for a clear and succinct answer!
    – pepoluan
    Jun 28, 2013 at 17:16
  • 1
    Fair enough. Glad to help!
    – squillman
    Jun 28, 2013 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.