• Sign In
  1. Messages

MS SQL Server setup

Installation Forum (Inactive)

View Message

view list print
MS SQL Server setup Lind-Thomsen  2015-09-24 01:43
Status: Closed
 
Hi
I'm still having problems with getting a running Labkey installation using MS SQL Server.

The basic questions are:
1: What is the preferred process to install a Labkey server using MS SQL Server when you don't have full permission/control of the server and want to be certain that the labkey admin have all the necessary permissions (at the moment the labkay admin is owner of the database) ?
2: Can you describe what permissions are needed (besides from being owner of the database) in the database to successfully run labkey?
3: Must it be the labkey server that runs sp_updatestats or is it OK if we have the option 'Auto update statistics' enabled on the database?

Background:
environment: MS SQL Server 2012, tomcat 7,windows 8 server. Manual install of Labkey

I'm not sysadmin so I don't have full control over the database, I have to ask our central IT department to do stuff, which takes 1-3 days depending on how busy they are.

There are (at least) two issues:

1: GROUP_CONCAT
Needs to be part of core schema, but the core schema cannot be made before the initial run of the server.
I get an error if I try to create the core schema in advance and assign the GROUP_CONCAT functions to it before I start the server ("core schema already defined" exception). After moving GROUP_CONCAT back to dbo and remove the schema. I get another error "DROP ASSEMBLY failed because 'GroupConcat' is referenced by object 'GROUP_CONCAT_D'" but I was able to start labkey. Then it seemed to work but when I installed a module I was back to a previouos discussed "FastaAdmin already exist" exception.

2: sp_updatestats
Need permission to run this stored procedure(in our system we don't have this pr default), this must be assigned before creating any database objects. Is it important who runs this procedure, as long as it is run?

Will the following process work (using the labkey admin user):
1: create the database
2: Set permission to run sp_updatestats
3: start Labkey server and finish the basic installation process.
4: install GROUP_CONCAT
5: Move GROUP_CONCAT to core schema
6: Restart server

Appreciate any comments and suggestions.

Best wishes

Allan
 
 
dennisw responded:  2015-09-24 13:12
With our TrialShare system (whose core is LabKey Server), our experience with sp_updatestats has been 'as long as it get's run'. On our two lower environments, for example, we have two LK databases on one MS SQL Server, so we let one of those users have rights to run it (which as I understand runs it for the whole server) and one that doesn't. Otherwise it would get run twice nightly. You'll get an exception listed in your Primary Site Log File every time you run maintenance if the LabKey user isn't sysadmin but it doesn't seem to hurt anything.

From what we've seen, you really need to have the LabKey DB user be DBO on the actual LabKey DB... but that user does not necessarily *have* to have higher priveleges on the SQL Server as a whole.

I'm afraid you may have somewhat of an uphill battle explaining to your IT folks that your LabKey database should be treated differently in some ways than a standard RDBMS. We let the LabKey Tomcat user run our LK DB's and interfere with them as little as possible.

Good Luck!
Dennis
 
adam responded:  2015-09-26 09:26
As Dennis says, LabKey doesn't really need to run sp_updatestats. We added it to nightly maintenance five years ago because a customer experienced performance problems due to outdated statistics, even though "auto update stats" was on (https://www.labkey.org/issues/home/Developer/issues/details.view?issueId=10067). That would have been in the days of SQL Server 2005 and 2008, so perhaps the underlying problem was fixed in more recent SQL Server releases. You could disable the "database maintenance" task under system maintenance. If you do this, you'll want to work with your DBAs to ensure they monitor and/or update statistics themselves.

Ideally, the user executing LabKey Server installs and upgrades is authorized to execute sys.sp_configure and install CLR functions. If that's not possible then install LabKey Server, run our group_concat install script (group_concat_install_1.00.23696.sql) manually, and then restart LabKey Server. These steps work equally well; I just tested them locally.

In your last message you talked about installing GROUP_CONCAT and then moving GROUP_CONCAT to the core schema. Are you running group_concat_install_1.00.23696.sql to install the functions? If so, there's no reason to move anything; that script creates the necessary functions directly in core.

Adam