1 Topic by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Topic: SQL Database size

I am worried, that at the moment I am running Recorder 6 on SQLServer 2005 Express, which has a file size limit of 4mb - my Recorder database is now 3.8mb in size.

On our Windows server we already have SQLServer2008 running which would give a maximum size of 12mb - is it possible to transfer Recorder from one version of SQLServer to the other.

Secondly we will be changing our Windows Server early next year, and will probably get SQLServer 2012 installed, will Recorder run in SQLServer 2012?

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: SQL Database size

Craig

I am pretty sure that transferring Recorder over to a different server should be possible and I think 2008 should  be no problem (maybe wait for confirmation of this though). I hear thave v6.18 is due soon and I think I would wait for that, just in case there are 2008 isues awaiting a fix. As to how best to do it, I am not sure, but someone else is sure to know. It might be best to do a backup, then reinstall recorder with an empty db in the new server, then restore from the backup.

As for 2012, I do know, cos Sally said so yesterday, that R6 is as yet untested with 2012.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: SQL Database size

It will certainly work with 2008 and is well tested on this. It will run SQL Server 2012, but we know there is at least one problem and further tests are required to identify all the issues.

Mike Weideli

MikeWeideli's Website

4 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: SQL Database size

Mike,

What is the procedure for moving from 2005 to 2008, I don't want to corrupt my data by doing it wrong!

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

5 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: SQL Database size

Intall SQL Server 2008, if necessary. You will need SQl Server Mangement tools installed though.  Detach the database from the SQL Server 2005 instance using the Management Tools . Copy the file at this point for security, but no need to move it.      Attach it to the SQL 2008 Instance using SQL sever tools. Change the Registry to point to the Sql Sever 2008 instance. Typically the registry entry is in HKey Local Machine/Software/DorsetSoftware/Recorder 6. Change the Server Name to the SQL Server 2008 instance. Execute the following query using New Query in the Management tools under datbase NBNDATA 

exec sp_change_users_login 'update_one', 'nbnuser', 'nbnuser'

If you need any more help on any area of this, plesae ask.


Mike

Mike Weideli

MikeWeideli's Website

6 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: SQL Database size

Mike,

The transfer went smoothly (too smoothly?) until the final query where on executing I get the following error message:

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Login name 'NBNUser' is absent or invalid.

NBNUser (I also tried your original nbnuser) does exist as a user and a schema - a search of the Internet suggested deleting NBNUser - this did not fix the problem and I then got a permissions error and could not recreate NBNUser, I have returned to the back-up and SQL 2005

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

7 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: SQL Database size

I think I know what this is. The attched SQl should fix this. Run it in place of the script I gave you before.  If it doesn't then it can be done manually, but i will need to work out the instructions. Definitely suggest you don't try to update to 6.18 before moving your database as the new table takes up a bit of space. 

Mike

Post's attachments

FULLNBNUser.sql 724 b, 5 downloads since 2012-12-20 

You don't have the permssions to download the attachments of this post.
Mike Weideli

MikeWeideli's Website

8 Reply by ser (edited by ser 20-12-2012 13:26:24)

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: SQL Database size

Oops, thought I'd do the update whilst waiting for this to be solved - luckily it hasn't gone over the 4gB, in fact it is almost exactly the same size, just under 3.8gB!

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

9 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: SQL Database size

Did you run the Dictionary update. This is what mightl take the space. If not it will not matter if you leave it as Recorder will function without the Organism table being populated.

Mike Weideli

MikeWeideli's Website

10 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: SQL Database size

Mike,

That script sorted the problem, .... however, on running Recorder I get the following error message:

An error occurred whilst starting the application.
EOleException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

11 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: SQL Database size

Mike,

At the moment, I've found I can't get SQL Server to actually run hence the  error above!

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

12 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: SQL Database size

Our ITC just told me that we now (or soon will) have SQL Server 2012 installed. He's quite keen to move Recorder across (it is currently on SQL Server 2000).

Is there any further news on the viability of this transfer?

I am still intending to remove R6 from our server and all workstations and do a completely fresh install (to try to solve a lot of legacy issues with workstation installs on 64 bit machines) and restore from backup, but am hoping to wait until the release of a full installation download, which I understand will be created for 6.19. I think it would be best to hold off from the SQL Server upgrade until that time. Obviously I don't want to do the latter until all issues have been resolved,

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

13 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: SQL Database size

There is only one known issues with SQL server 2012. This follows testing of all major parts of the sytem, but there could well be others problems lurking. The one that has been found is obscure and could easily have been missed. The known problem is with the report wizard, where using the back button after completing a report causes an error. It is on the list to be fixed in release 6.19.

Mike Weideli

MikeWeideli's Website

14 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: SQL Database size

Oh well Mike, you know how much I love being a guinea pig.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

15 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: SQL Database size

Mike,

In my other post (here http://forums.nbn.org.uk/viewtopic.php?id=4834) I mention that I had to use the query you posted above to create a new NBNUser login. It worked fine thanks.

However the problem which we fixed by doing so was only present on workstations running under XP. Our W7 machines don't have the problem. I am curious about this. Why should it be different with these two operating systems?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website