Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

DBcreatewizard or just run good old SetupOM.exe - which should I use to install the Database component of OpsMgr?

DBcreatewizard or just run good old SetupOM.exe - which should I use to install the Database component of OpsMgr?

  • Comments 13
  • Likes

There has always been a bit of confusion on when to run the DBCreateWizard.exe tool, or when to just use SetupOM.exe to create the Operational DB or Data Warehouse DB.

Historically.... in MOM 2005, we used the DBcreate Wizard in order to create the Onepoint database on Active/Active clusters..... or when SQL DBA teams refused to run a MSI based setup on one of their SQL servers.  The DB create wizard was a better option for them.... since it did not have to install any binaries on a SQL server.  In practice.... it was pretty rare to see this in widespread use.

 

In OpsMgr 2007, we haven't really documented all the scenarios for when you should run the DBcreate Wizard.... and I will try and do that here. 

 

The DB create wizard is located on the CD - In the \SupportTools folder.  It does require some additional files to run it - these don't have to be "installed", just need to be copied over to the SQL DB server where you will run the wizard.  Follow:  http://support.microsoft.com/kb/938997/en-us

***  Note - the additional files required to run DBCreateWizard.exe are documented in the KB article above.  They were also provided on the SP1 Select CD.  However - the files provided on CD are for 32bit x86 only.  If you are using the DBCreateWizard on a x64 platform - you MUST copy these files listed in the KB article from an x64 server.... any x64 server with the console installed will have them.

Note - there were some significant issues with the RTM version of this tool... in detecting the correct SQL instance on a multi-instance cluster, and leaving some table information blank (http://support.microsoft.com/kb/942865/en-us).  When deploying SP1 - Use the SP1 version of this tool.  If you MUST deploy the RTM version - I would recommend using SetupOM.exe for all installs.

 

Ok.... first, you will notice in the OpsMgr Deployment guide, they instruct to use the DBcreateWizard when installing the database on an Active/Passive cluster.  That's pretty much our first introduction to this tool.  While this isn't required (you can simply run SetupOM.exe on the Active node) it is recommended to use DBCreateWizard.  Essentially, our recommendation is that anytime you have a dedicated SQL server for the OpsDB role... with no other OpsMgr role present, then you should use the DBcreateWizard to create the Operational database.  The reason for this, from an internal discussion I have been involved in.... is because using SetupOM.exe will create some additional registry entries on the database server... and will change how updates are applied to the server from an OpsMgr perspective.  Another scenario to leverage this tool, is anytime your SQL DBA teams refuse to allow you to run a MSI based setup on their SQL servers/clusters.

 

Below, I will just walk through some of the scenarios where using this stand-alone tool really makes good sense.

 

Scenarios:

1.  All in one role/shared roles.  This is where a single server hosts SQL Server 2005 and the Operational Database role, along with the RMS role.  In this case.... you might as well just run SetupOM.exe and create the database while installing the management group.  You potentially could run the DBcreatewizard first.... but this would be an additional step and provides no value.

2.  Split roles:  Dedicated SQL server (Server A) and dedicated RMS (Server B).   In this scenario - we recommend using DBcreatewizard.exe instead of just running SetupOM.exe on the SQL server.   However - you certainly can do either one.... both are fully supported.

3.  Split roles - clustered DB:  Dedicated cluster for SQL (can be A/P or A/A or multi-instance or multi node.... doesn't matter)  In this scenario - we recommend using DBcreatewizard.exe instead of just running SetupOM.exe on the SQL server.  That said.... you can run SetupOM.exe on any node that owns the SQL instance you are creating the DB in.... we just favor using DBcreateWizard.

4.  Draconian DBA's.  In general.... DBA's are used to creating an empty database for an application, then granting permissions to the DB only.... then washing their hands of it.  They don't like running setup's... or even running tools on their SQL servers....  If they must have an application create a database as part of that application install - they MUCH prefer that all the DB creation be handled remotely.  Unfortunately.... MOM 2005 and OpsMgr 2007 do not support what DBA's would most like to see.  We must run our setup or tool on the database server/node in order to install that component.  I suppose we could install the OpsDB using the DBcreatewizard in a test lab SQL box, then detach it.... then hand the files to a SQL team and have them drop in into a production environment to make them happier.... but I haven't really done much testing there.  Anyway.... the DBcreateWizard is the best option when working with a rigid DBA team.  Just follow the KB article listed above... and have the SQL team run the tool to create the database.... then they can delete to tool from the server.  We will still require SA priv over the instance to complete the RMS setup.... but once that is done, they can remove these advanced rights, per my previous post:  http://blogs.technet.com/kevinholman/archive/2008/04/15/opsmgr-security-account-rights-mapping-what-accounts-need-what-privileges.aspx

5.  Multiple Operational Databases in the same SQL instance.  It is possible, if you have multiple management groups, that you could place all the Operational DB's into a single SQL instance.  Now - these had better be small environments (test/dev) or a beefy SQL server to handle all that I/O.... but just for grins.... lets say you are doing it.  If you tried to run SetupOM.exe and install the database component multiple times.... it would detect it was already installed and ask you if you wish to repair or remove OpsMgr.  No good.  In comes the DBcreateWizard.  This tool is the supported method for creating multiple OpsDB's in a single SQL instance.

Comments
  • Source: Raymond Chou Raymond has posted an article about some Design Tips for OpsMgr 2007. I totally

  • Hi Kevin.

    Thx for the great post.

    I have a small issue with the db creation wizzard.

    Every time I run it for creating the "OperationsManager" database it will say it's created successfully on the cluster but no database is actually created and I have no error message whatsoever.

    Any ideea ?

  • I had the same experience as George above - success on the wizard, but no DBs created. My server is a Windows Server 2003 R2, SP2 and is a 64-bit machine.

    One thing I do see in the message above this statement:

    "...files provided on CD are for 32bit x86 only.  If you are using the DBCreateWizard on a x64 platform - you MUST copy these files listed in the KB article from an x64 server.... any x64 server with the console installed will have them."

    The thing is - I am just starting the install, so I don't HAVE a console to copy the files from... If I find a solution I will post it here...

  • OK, here is what I had to do to get this working...

    1. Install the console only (no need for the PowerShell component) on any Windows 2003 64-bit box

    2. Copy the following files to the Windows 2003 64-bit SQL 2005 box

    DBCreateWizard.exe

    Microsoft.EnterpriseManagement.UI.ConsoleFramework.dll

    Microsoft.MOM.UI.Common.dll

    Microsoft.Mom.UI.Wrappers.dll

    MOmBidLdr.dll

    3. Run the wizard.

    a. Be sure you DO NOT change the default DB size of 500 MBs

    b. If you run the wizard and the DBs don’t create, check the event log. If you get the message below, make sure you set the NTFS permissions on the directory where you are creating them to Everyone:F, then re-run the wizard.

    Event Type:        Error

    Event Source:    MSSQLSERVER

    Event Category:                (2)

    Event ID:              5123

    Date:                     12/9/2008

    Time:                    8:48:20 PM

    User:                     xx\xxxxxxxx

    Computer:          SQL57

    Description:

    CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'D:\SQLLOGS01\OperationsManager.ldf'.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Data:

    0000: 03 14 00 00 10 00 00 00   ........

    0008: 0b 00 00 00 53 00 31 00   ....S.1.

    0010: 57 00 50 00 50 00 53 00   W.P.P.S.

    0018: 51 00 4c 00 35 00 37 00   Q.L.5.7.

    0020: 00 00 00 00 00 00         ......  

    4. Move our MDF/LDF files to their proper destination, if necessary.

  • Hi, Kevin

    Thank you for the useful information.

    would like to ask some questions here

    1. Is this tool the supported method for creating multiple Data warehouse database in a single SQL instance?

    3. Is this tool support creating opsDB and opsDW DB in a single SQL instance?

    2. could you share the procedures for installing multiple opsDB or opsDW DB in a single SQL instance?

    Thank you

  • 1. Is this tool the supported method for creating multiple Data warehouse database in a single SQL instance?

    KH - I dont know about that - as that is an extremely rare circumstance.  In general the data warehouse should be in it's own dedicated instance.  In multiple management group scenarios - it is more common to see them all share a single data warehouse.  Can you describe the topology and business case to have multiple warehouses in a single SQL instance?

    3. Is this tool support creating opsDB and opsDW DB in a single SQL instance?

    KH - you can - but you can also use regular UI setup to do that as well, which I would do.  

    2. could you share the procedures for installing multiple opsDB or opsDW DB in a single SQL instance?

    Just run the tool again - and specify a unique name.

  • Thanks for your reply, Kevinhol

    We have two groups, one is in China and the other is in USA. We would like to centralize the data warehouse DB in US. Thus, we will have dedicated RMS and OpsMgrDB for each group and share one dedicated SQL server for data warehouse DB, that means both DW will hosting on one SQL instance.

    Looking forward to your comments.

    Thanks

  • Why have two DW at all?  Why not have both management groups report data to the same reporting data warehouse?

  • Dear Kevin,

    Here is the scenerio, what happen when the Operation Manager DB is deployed on an active cluster node using setupOM.exe? will there be any side effects of it? what happen when the failover occours? would RMS be able to access the operation manager DB? logically it should.

    During RMS installation using setupOM.exe on the seperate server, the setup recognized the clustered SQL instance successfully and setup connected / updated the clustered DB running behind the clustered SQL instance name.

    Would registry entires that were created on the cluster node during setup create any problem during / after failover happen?

    If yes would there be any simple way to fix the post issues? or the complete installation requires for the HA of DB?

    Thanks

    Dj

  • Q:  what happen when the Operation Manager DB is deployed on an active cluster node using setupOM.exe?

    A:  No problem - as I stated - you dont HAVE to use DBcreatewizard, and running setup is fine.  There shouldnt be any major side effects, to failover... because this is clustering taking over.  The registry entries created during the running of SetupOM.exe to install the DB on a cluster wont affect cluster operations.  When you installed the RMS - you pointed to the SQL database virtual cluster name.... at least - you should have.  :-)

  • Being a "draconian DBA" I have to ask, why isn't the it possible to point SCOM at pre-created databases, and why are sysadmin rights required?

    Any responsible DBA will want to know what changes a software installation is going to make, and requiring sysadmin without an explanation will immediately raise concerns about what exactly is going to be changed.

    I've had experience with many other packaged applications so I know it's possible to do things the "right way", but it seems like MS is the worst offender when it comes to following best practices.

  • Hi Mike,

    I hope you detected the bit of satire on this subject - in my post.

    In general - OpsMgr SQL instances are designed to be dedicated - to nothing else other than OpsMgr.  Following best practices - they should really be placed into dedicated clusters, or at least on a cluster where their SQL instance is dedicated alone to a node.  For these reasons... this should be less of a concern.

    SA and local admin is required - because this is NOT a typical "back end" database application.  The setup was written to create the DB, apply permissions, write out the files to the OS, and set up the initial database schema.  All of these changes to the instance - require SA.  Granted - theoritically, the setup application could have been written to allow a DBA to set up ALL of this manually.... but then - this would make setup for 90% of the customers out there a LOT more problematic.  We arent bypassing any security... these rights are ONLY needed for setup - once deployed - these advanced level rights can be removed from the installing user account from the SQL instance.

    Generally - when I find a SQL team that is pushing back on a security issue - it is simply that they are trying to mold the SCOM application fit the DBA "mold" - and implement SCOM into a SQL environment/cluster that is shared with a LOT of other instances/databases... which causes the concern of granting advanced rights.  

  • Thanks for the reply.

    Actually, the setup can very probably do everything it needs without SA including creating databases, applying permissions and setting up the database schema. SA would only be required if changes were required at the SQL instance level. I'm in an environment where we try to consolidate as much as possible, and you're right it's these sort of things that make us nervous. Having an application account be DBO is much preferable to giving it SA for the install. However if SCOM isn't recommended to share the database server that's good to know, and it would likely be isolated in our environment anyway due to the capacity requirements.

    It should still be possible however to do everything that needs to be done in setup by pre-creating the database(s) and having the DBA create the logins with the proper rights to those databases. We had the same issue with Sharepoint initially until we came across a document describing how to install it with databases created by a DBA.

    I'd be happy to help with this install process to make it more DBA friendly, or I'd imagine you could ask the SQL Server development team directly. :-)

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs