Scott Osborne, CTP, Omaha CUGC Leader
I have been meaning to get this out there for a few months now, but since most of my time during the week is spent onsite all over with customers and on pre-sales meetings, I tend to not be home much to get it done. Weekends for me are 100% all about my one and four-year-old princesses and our house remodeling, so needless to say, this leaves me little extra time for writing things like this up, unfortunately. I have posted a few times for others to help so with this being end of the year and it being a bit quieter on this front, I wanted to get it cemented out there formally. Nothing fancy; just getting it on paper now that the new www.mycugc.org blogs site is live as of last week! I get to be the first to go through the process here on this new site and so far, so good. 🙂 I would say check the new CUGC site out, but if you are seeing this, well…
Since I have had to go through this pain for a few installs and there hasn’t been any real documentation on how to get through this process, here are the highlights of the steps I am following that works without issue. Note that all this difficulty is due to WEM adding an encryption key to the database (which appears to have been the case for quite some time). You can see this key by running the query: select * from sys.symmetric_keys against the master database. The SQL ‘database state’ itself will NOT show the database being encrypted. It just has an encryption key inserted during the install which is enough to break being able to establish an AG easily as it will complain about needing the ‘database master key’ in order to successfully create the AG through the wizard. This master key is NOT the password for the vuemUser or anything like that. I repeat – the whole database will NOT show up as encrypted if you look in the GUI. I have seen other articles having the process prompt for a password which then would be the vuemUser account password, however in 3 or 4 instances now, this complication has come up. I don’t know if is specific to a SQL build or simply just the order of events from the SQL team, but either way, this process will work for establishing the DAG or BAG if you are getting the error about needing this ‘Database Master Key’.
I have noticed this Database Master Key shows up in older builds also, but my suspicion is that this is popping up as an issue quite a bit at least for me with WEM now supporting Availability Groups with recent releases. Whereas before, with older builds not supporting it, this scenario just wasn’t something that came up since BAGs/DAGs were not on the supported list.
I won’t be going through each and every detail from A to Z, so this assumes you have at least got through the normal stuff and know what I am referring to. Hence, these are my ‘B to G’ steps–meaning I will certainly not touch on each and every step from beginning to end, but rather just wanted to get the meat of issue at hand on paper for everybody in case you run into it; at least for what has worked for me. You would do this same process for however many nodes are in the cluster. Of course, if just a Standard BAG, you will just have the two nodes.
- Create the database using WEM configuration utility (need SA) / AD service account on the first node in the availability group. NOTE: You must NOT use any listener for this and instead point directly to the SQL server/node acting as primary. You might have already figured that out the hard way already if a SQL admin already created a blank database and established AG and listener and gave that to you instead.
- After the initial database is created/seeded by the install, the SQL team (or you) can then replicate the database going through the following TSQL steps to create the availability group if/when you run into the issue with it prompting for the ‘Database Master Key’. SQL studio wizard apparently doesn’t like doing it, but manually going through commands works fine http://www.macaalay.com/2017/01/20/workaround-for-adding-encrypted-databases-by-a-database-master-key-on-high-availability-groups-without-a-password/
- You can then add the domain service account to the other node(s) using the normal add account in the SQL Studio MMC.
- Then replicate the local vuemUser account. Something that IS mentioned in the WEM installation docs, of course, is to specify the vuemUser account password so you should be familiar with this account. This is really only needed for the Availability Group scenario. The vuemUser is special and needs to be copied from the initial node where the database was created so that the SID for it is retained as it is a local SQL account rather than an AD account. You can’t just manually create the same named local account on the subsequent nodes in the cluster. I don’t think you would notice this being an issue until maybe a failover event. Details of this overall process can be found here with my quick cliffs notes summary below it: https://www.sqlmatters.com/Articles/Copying%20logins%20and%20passwords%20from%20one%20SQL%20Server%20to%20another.aspx
- Run the following script discussed in Method 2 to add the stored procedures needed to do so. https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
- Run exec sp_help_revlogin ‘vuemUser’
- Take the output and run this on the second server (and any others if more than two nodes) to create the local user account and having it retain all properties This is the example to work from: https://www.sqlmatters.com/Articles/Copying%20logins%20and%20passwords%20from%20one%20SQL%20Server%20to%20another.aspx
- At this point, the Availability Group itself should be established and looking good with everything ready to go. Other WEM servers/brokers you add can then be configured using the wizard to use the listener rather than direct from that point forward in order to join. You should also go back and set the first WEM server to hit the listener using the wizard. It should pass the test and connect successfully.
That’s it for now. Merry Christmas, Happy New Year and Happy Holidays in general!
And to all my #nitro brothers that are on the partner side, see you soon at PTEC/Summit/Servtech in a few weeks!