Command Shell Examples
Useful SQL Queries
Can the SCOM databases share a SQL instance with other databases? - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Can the SCOM databases share a SQL instance with other databases?

Can the SCOM databases share a SQL instance with other databases?

  • Comments 2
  • Likes

Just wanted to answer this question here as a pointer for later reference.  But before I continue, I want to mention that this is from the perspective of SCOM.  You’ll need to read documentation of your specific application before collocating your other database with the SCOM db’s.

 

The biggest concern will be one of performance.  There are many contributing factors to deciding whether this will provide acceptable performance – hardware, configuration of disks, configuration of SQL instance(s), what is the transaction volume of the other databases sharing the host, how many agents will be deployed, is the SQL host virtualized, which management packs will you import?

 

If you plan to install more than 100 agents, this alone might put you into a situation that will create a poor experience in the console and cause a bottleneck on the SQL instance.  I’ve seen implementations where nothing but the IIS, Windows and Exchange 2010 MP’s were installed, under 100 Exchange 2010 agent-managed computers, and it was evident there were bottlenecks on the SQL Server – and this was only shared between the SCOM databases.  Keep in mind that the SCOM databases are a type of OLTP database – read characteristics of OLTP as SQLCAT explains it.

 

There are no hard and fast rules against sharing a SQL host for the operational and data warehouse databases – it all comes down to performance.  Good news is, you’re never tied to a SQL instance for the SCOM databases, since it’s a relatively easy move procedure.

 

Move the operational database:

http://technet.microsoft.com/en-us/library/cc540384.aspx

 

Move the data warehouse database:

http://technet.microsoft.com/en-us/library/cc540368.aspx

 

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • Hello,

    I have the OperationsManager database in a sharing instance (MSSQLSERVER) on a SQL Cluster with the RMS on a separate server. As in CU5 I saw in the CU5 logs a SQL script:

    DECLARE dbnames_cursor CURSOR

    FOR

    SELECT name from sysdatabases

    where (DATABASEPROPERTY(name, 'IsOffline')=0 AND

          DATABASEPROPERTY(name, 'IsShutDown')=0 AND

          DATABASEPROPERTY(name, 'IsInLoad')=0);

    OPEN dbnames_cursor;

    DECLARE @dbname sysname;

    FETCH NEXT FROM dbnames_cursor INTO @dbname;

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN

      SELECT @dbname = RTRIM(@dbname);

      EXEC ('USE ;if EXISTS (select * from sysobjects where name=''__MOMManagementGroupInfo__'')

      SELECT db_name(), ManagementGroupName, DBVersion from __MOMManagementGroupInfo__');

    END;

     FETCH NEXT FROM dbnames_cursor INTO @dbname;

    END;

    CLOSE dbnames_cursor;

    DEALLOCATE dbnames_cursor;

    As it is running on the sysdatabases I wonder if it affects other databases within the same instance?

    We have the OperationsManager database in a SQL Cluster sharing the MSSQLSERVER instance.

    Also the SQL Service with be restarted as well, isn't it?

    Any idea?

    Thanks,

    Dom

  • Dom - I have not heard of any issues with running the SQL scripts on SQL instances that host more databases than just the Operations Manager database, and this script should only effect elements related to the OpsDB.  Of course, anything could probably happen - personally I wouldn't be too concerned about it.  The best way to verify this is to run this scenario in your lab and verify your other DB is still operational.