Upgrading SCSM 2012 R2 to 2016 and replace Data Warehouse (server or all)
We're in the planning of upgrading SCSM 2012 R2 to version 2016 including different Cireson products.
Our Data Warehouse Management server is still server 2008 R2 and has an old server name that doesn't match the new naming convention any longer. I know that it's not possible to move the DWH Management server to another server that has a different name, so by upgrading to 2016, I would have to stick to the old name. According to some information, it won't be possible to create a complete new DWH and import all existing data into the new DWH. We need to keep certain information for as long as 10 years. Having 2 different DWH is also not an option.
Does anybody know whether it's possible to insert old DWH data into a new DWH database, contrary to the information I got? Is there possibly another solution to setup a different kind of DWH, import the old data and connect it with the new DWH server? I'm not at all satisfied with the existing SCSM DWH, it's too unstable and needs a lot of effort. SO I'm open for all kind of ideas to upgrade the 2012 R2 environment to 2016 and maybe get a new functioning DWH.
Thanks for your tips.
Having said that, I thought that you could restore the DW to a new server, then install a new DW mgt server (or uninstall/install again on same server) with the existing DB's encryption key. The procedure is here: https://docs.microsoft.com/en-us/system-center/scsm/implement-disaster-recovery?view=sc-sm-1801
What this article does not cover is the fact that the server names would change. I invite corrections from everyone else, but I believe this would work (try in a lab first!):
unfortunately, it's hard to find information about not changing the server name for DWH management server. This article here http://aquilaweb.net/2015/05/03/ (first link on the page) has the part that's missing in all other articles "the new server must have the same name as the original server". According to MS, it's the only way, renaming the DWH server is not possible. They've just forgot this important part in all other documentations.
I actually wanted
You are not the first person ever to experience the pain of DW. It can be very frustrating.
Firstly, to my knowledge, what you are trying to do including the renaming of the server is NOT possible. Those few articles you can find are correct, that the server must be called the same name.
Therefore, I wondered if you had considered the option of using Cireson Analytics as your long term archiving solution? Its the complete opposite to DW, super simple to operate but you only get a super simplified set of data. It might not suit your needs but it probably worth investigating. Unlike, DW, you can just inject data right into it via SQL statements so you could pull data from current DW and insert it in to get a continuous record.
Have a read of my blog here all about it, to get an understanding of what is can and can't do and as always, let me know if you have any questions.
we've already considered Cireson Analytics, but it's not what we're looking for in terms of available data. We need to keep user names for as long as 10 years, we need access to Incident action log entries and Service Request action log entries that can be transferred to DWH. The audit feature is necessary, we work in a sensible environment.
While it isn't addressing the core of your question and I could be overlooking something here entirely at the very least I'd say extend your DW retention time out to 10 years if you haven't already.
DW Troubleshooting is certainly a skill in and of itself for sure, but what kind of issues are you running into? Just different jobs failing?
I'm struggling with failing jobs and cube processing. There's not a single month where I don't have to run manual job and/or cube processing because something got messed up. And it's not even sure that after manual processing, everything works again as expected. In the beginning, I worked together with MS to get the DWH stable again, meanwhile I know what to do most of the time. I'm just tired of doing this manual stuff and would love to have a solution that just runs by itself and doesn't need as much attention as the DWH.
I know that there might not be another proper solution because of our regulations to keep specific data for 10 years. I just wanted to be sure that I do not miss a good solution outside of the regular DWH which would save me a lot of time and effort.
Example of "controls"
We as the product owner do not allow the patching group to control patches to our SQL and SCSM farms. They publish for us but we control when they get installed.
To that before any patching on SQL or SCSM starts we run a PowerShell script that disables all the DWH schedules (while monitoring for jobs to complete) once all jobs are completed and the schedules disabled we start patching and performing SQL fail-overs.
Once done we have another script to restart everything.
(This script is a derivative of the MS published DWH repair script)
sounds good, but I don't want to sit around during night once or twice per months to control patching for servers. Maintenance windows are in between 10pm and 5am, mostly during week-ends.
The DWH even stops working properly without anybody doing anything to the servers. I do not see a real relation between patching times and DWH breaking. If I'm getting upset too much in the next couple of months, I might give it a try to control the patching.
I know the MS script to do the repairs, but once in a while even this script won't help in first place. Sometimes, I just don't worry about this DWH and let it run with its "partially processed" or other errors for a while. This inconsistency is the reason why I was hoping for another tool to replace this DWH.
Another option would be just to run the scripts to disable the schedule before the window and restart after completed.
Jobs running during patching will break the DWH every time. I have spent hours repairing the DWH when this happens.