The second part of our 3 part series: The Care and Feeding of Blackbaud CRM™
“Is your CRM sleeping through the night yet?”
That is, are you sleeping through the night while your CRM refreshes, global changes, ETLs, and syncs merrily along? If your answer is no, and you currently have an alarm on your phone set to 3am so that you can manually check or kick-something-off, it is time for a performance overhaul.
Prior to go-live, it is common to design the nightly processing and maintenance windows so that they do not run into one another, and dependencies are met which will ensure the success of the entire schedule. Post go-live, it is common for that nightly schedule to fill up quickly with additional processes, and an ever-longer ETL or sync. As your database grows, as your users explore the system, and as you add to the lists of Events and Marketing Efforts, your syncs tend to take longer.
… and somehow you find yourself waking up at 3am checking the queue…
Performance, order of operations, and process inefficiencies are to blame. Now is the time to take a step back and get reacquainted with your nightly schedule so that you can rest easy knowing that CRM is happily chugging along.What is even happening at night?
We schedule processes and maintenance to run at night for a few reasons:
The processes might take too long to run during the day
They might interfere with normal business-day operations (like processing revenue)
They require that the data in the database is static while the process runs
Hardware-intensive processes, write-processes, record updates, ETL to the data warehouse, and Acknowledgements to prep for the next day’s fulfillment fill the bulk of the nightly schedule. Sneaky queues like Global Changes and Marketing Effort refreshes squeeze into the spaces between, filling every nook with a nested queue. Unfortunately there are days when, if one queue takes longer than expected, it throws off the cycle time of the entire nightly schedule. Or worse, it causes every subsequent process to fail.
How do we untangle it all?
The best way to begin is to lay it all out. Start with the schedule as it is currently designed. Forget the start and end times of each queue or process, and focus instead on the order of operations. Analyze your dependencies to identify those that are critical.
Does your nightly currently run serially, where only one process is running at a time?
Are the dependencies real, or assumed dependencies? (For example, do you really have to run Constituency refresh before running Acknowledgements? Does the Marketing Effort refresh queue need to happen before the ETL to the Data Warehouse?)
Which processes are positioned within the schedule to hold up the entire process?
Which processes must be run when nothing else is running? (A database copy comes to mind…)
From this information, you can outline a plan to tweak and enhance the schedule so that it runs without your supervision.
Setting up a new nighttime routine
Only seeing it deconstructed will allow you to make changes to your nightly schedule that will result in more efficient and less unpredictable cycles. Take a look at each process independently.
Use SQL Spotlight running in the background during your nightly schedule - it can provide valuable insight about the server and application performance during each process
Consider rewriting the underlying queries to be more efficient
Review the data lag - not all processes need to run daily in order to provide sufficient data for your users
When looking at dependencies, are there any processes that can run simultaneously to free-up a window within the schedule?
Can some processes run partially during the week, and in full over the weekend?
White Space - blessed silence: Any opportunity to inject some down-time into the schedule will give the processes some breathing room in case they do run long, and will give you opportunities to make ad-hoc additions when needed
The next time you find yourself opening your laptop in the wee hours, consider planning an overhaul of your nightly schedule. With some performance analysis and enhancements, de-coupling some dependencies, and infusing some quiet into the hum of the queues, you will be on your way to resting easy while your CRM does the hard work.
Still can't sleep? Give us a shout, we're probably awake too (and in case we're not, we'll get back to you in the morning with some additional information about how we can help your organization.