1

Re: Swapping in and out different databases

I've just worked out some code (C#) to automate swapping different versions of the NBNData database (on SQL Server Express 2005) prior to running Recorder.

Is this something anyone else is interested in/has any experience of?

Or any suggestions of hidden pitfalls I should be aware of in swapping databases?

Cheers

Alan

Alan Hale
Aberystwyth

2

Re: Swapping in and out different databases

Yes please, I'd love to see that code (I'm currently using a .reg file).

There are some pitfalls. Mapping springs to mind. Perhaps John van Breda could give us a clearer picture? I've not run into any serious problems though, although I only ever use secondary databases for testing or reporting purposes.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

3

Re: Swapping in and out different databases

What I normally do is write a .reg file that changes the server and database name, plus points the Object Sheet File Path and Map File Path to a unique directory for each database. Then I write a small batch file that runs the reg file in silent mode then runs Recorder, and use the batch file as my shortcut. Other than making sure you have a unique Object Sheet File Path and Map File Path for each database you use I don't think there are other pitfalls.

Best Wishes

John van Breda
Biodiverse IT

4

Re: Swapping in and out different databases

This is basically what I'm doing (the code is attached to a button click event, a different button for each database):

           //Set up a StringCollection object to hold the paths to the MDF and LDF files
            StringCollection files = new StringCollection();

           //Add the paths - depending on which database you want loaded
            files.Add(@"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NBNData_Data.MDF"); 
            files.Add(@"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\NBNData_log.ldf"); 

           //Set up a connection string
            string connectionString = "Server=YourServerName\\SQLExpress;Trusted_Connection=Yes;";

          //Set up a connection
            SqlConnection connection =  new SqlConnection(connectionString);
            connection.Open();
            ServerConnection servConn = new ServerConnection(connection);

           //Set up a server object and connect to it
            Server sqlServer =  new Server(servConn);


           //Check if a database is attached first
           //If it's attached, detach it
                if (sqlServer.Databases.Contains("NBNData"))
                {
                    sqlServer.DetachDatabase("NBNData", false);


                    sqlServer.KillAllProcesses("NBNData");
                    sqlServer.Refresh();
                }

            //Attach the new database files
                sqlServer.AttachDatabase("NBNData", files, "YourServerName\\DBOwner",AttachOptions.None);
                   
                sqlServer.KillAllProcesses("NBNData");       
                sqlServer.Refresh();
         
            connection.Close();
         
            connection.Dispose();

I've left out any exception trapping for simplicity and I may have gone unecessarily over the top with closing, disposing and killing.


I have no idea how to set the Object Sheet Path and the Map File Path using this approach.


Any suggestions very welcome.

Alan

5

Re: Swapping in and out different databases

Ah, that's a different approach to the one I usually use. I usually do what John does; i.e., I have multiple databases (with different names) attached and I change registry entries to swap them over. The trouble with detaching and reattaching is that it's a disruptive process. If other users are connected, you have to kill their connections. This isn't a problem in a single-use environment, but in a network environment is problematic.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

6

Re: Swapping in and out different databases

Yes, I don't have the networking issue because I'm a single-user. But maybe your approach is the most efficient one in any case. I don't know.

Cheers

Alan

7

Re: Swapping in and out different databases

It would probably be quicker as there is some processing that goes on while SQL Server does the detach and re-attach. On the other hand, having multiple databases attached at the same time could cause SQL Server to consume more resources.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

8 (edited by Alan Hale 04-03-2008 17:52:33)

Re: Swapping in and out different databases

Can I just confirm whether you are both swapping in and out LIVE databases - i.e. you are making edits on different databases from a single copy of Recorder? (I've just been told of problems that have occurred with Marine Recorder apparently resulting from people swapping databases -

"causing a mismatch in data keys between copies when data is edited. This means that the data in separate copies of Recorder then cannot be merged or imported as the keys of the individual records then get corrupted."

As I understand it Marine Recorder is based on Recorder 2000 - so is there an issue here that does not apply with Recorder 6?

Alan

Alan Hale
Aberystwyth

9

Re: Swapping in and out different databases

I've only ever swapped between our live db and a test one. But I think for the keys to get "out of sync" you'd have to be entering data into two dbs that contained the same SiteID. This is a serious no-no however you do it. If you are swapping between two dbs that contain different SiteIDs, then I think you'll be OK. Could someone confirm/deny this, just to be sure?

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

10

Re: Swapping in and out different databases

Charles, you are absolutely right, to ensure no problems then every database should have its own site ID. Swapping between 2 databases with different site IDs will cause no problems as long as you also swap the Map Files and Object Sheet File Path directories.

I think there is a little bit of processing when attaching or detaching, but its minimal. It certainly happens very quickly on my set-up. Also, resources consumed by having several databases attached at one will be fairly minimal unless you are using both of them at once, in which case SQL Server will be sharing its memory cache between the 2 dbs so will be a little less efficient.

John van Breda
Biodiverse IT