Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 4 - SQL Server Requirement Analysis

D Walsham • 13 December 2021

Looking through the current SQL Server topology and how it affects our decision

Introduction

In this part we are going to focus primarily around the SQL server aspect in our decision making.


Taking the time to understand how your SQL server environment fits to not only just the upgrade task at hand but also the overall future of future releases plays a big part. Many moving parts when it comes to your SQL Server architecture so this part we will go into fine detail in all parts around this topic and how they contribute towards your decision of its state and whether it does make sense to either proceed with an in-place upgrade approach or side-by-side approach.


Does the SQL environment also fall into the category of upgrade or side by side

Yes! It absolutely does. But as this requirement is tied to our sccm decision making they all play a part altogether. The series itself yes is more for the design upgrades for SCCM in general for its topology and architecture changes, and SQL Server plays a huge part in it.


If serious design changes needed to the done for SCCM and SQL, then a strong case could be made to proceed towards a side-by-side migration. Of course in-place upgrades can be done but whilst it has the convenience of keeping everything as it is, it does come with many risks which can be reduced from careful planning and DR for each part.

How does the SQL Environment play a part

Firstly its about assessing the current SQL server environment in which you have and that comes down to many points such as


  • SQL Server Version
  • SQL Server Architecture for DR
  • Backup Strategy


More information specific to SQL Server requirements which may be referenced on this article can all be found here


SQL Server Version

Starting from the top lets look at the versions which we have. Looking at the official documentation from Microsoft ideally we want to be on SQL Server 2017 if we want to be able to support scalability for the future.


I would say the minimum requirement version which you should be on ideally would be for SQL Server 2016 and then recommended cases I would say SQL Server 2019. I have come across a lot of organisations which still do utilize a SQL Server 2012 environment and whilst you could still use this, you are running on the brink of being obsolete as well as this version being deprecated further into the new release of the SCCM CB versions when it gets to 2107.

SQL Server Architecture for DR (Disaster Recovery)

Next layer is now how we have it designed and its readiness for DR. Common options range from SQL Server AlwaysOn, SQL clustering and even down to legacy options such as the SQL database mirroring.


But the decisions and how you should have this setup can vary depending on the kind of role which SCCM plays in your overall environment. For example some organisations may not consider SCCM to being a business critical service being provided, there a single SQL Server instance is all they feel is required and in some cases even consolidated all on the same server.


Of course we have to understand to if or when that point does change then the options range increases as you would then need to take into account the following things;


  • Process of Migrating SQL Server from a consolidated Box
  • Removing Single Point of Failure
  • High Availability for the Database


Now for environments who have this setup already they are a step ahead in terms of overall checks and prerequisites for pushing forward. But there is still the case of actually performing a DR test or analysing the structure they have to ensure that not only high availability is supported but how efficient it is during a disaster situation.

SQL Backup Strategy

Quite an interesting topic here, as one of the most common things we see here can be what I like to call backup overlapping.


Not only do we have the options in SQL to backup our databases and logs, we also have the native backup options within SCCM which can take a backup of the database and site via a site maintenance task. This can sometimes cause a bit of an overlap because of how potentially BIG your SCCM environment is. If you have both configured and the database is typically 50GB for example, depending on your schedule and storage requirements you are essentially saving 100GB of duplicate backups per each run.


Before you proceed any further its best to sort this out asap because this is a creeper but it can start to get more annoying until it takes on the head of being extremely problematic.There is more to this section which we can cover in a separate article in other methods and more explicit areas of backup strategies too.

Reporting Services

If this is something which may not be utilised, then adding this into the fold isn't a huge deal.


But if you do perhaps use an older version of SSRS and have custom reports within this, then indeed you will need to pay particular attention to the sections mentioned above in regards to the key factors.


Upgrading SSRS can be simple but it can break very easily and cause the reporting services role to have to be reinstalled again, thus possibly losing any custom built reports and any linked report configurations which you may hold. Its best to make a backup of ALL of these kind of reports before you proceed with any change altering actions.

Data Warehouse Implementation

A new feature which has been available for SCCM for a little while which has not been used as much. Having the ability to have historical information within SCCM when it comes to deployments and various client health and activity information is extremely handy, but there are also a couple of things to consider in this decision also.


Firstly you are given the option as to which tables of information you want to capture historical data for and its retention period. These decisions will of course impact the requirements of your SQL Server storage requirements as having too much historical data can cause the same issues as stated above and taking into account that you will now have an additional database to which may need to be highly available if considered a business critical service.

Next on Part 5

The next part we will focus specifically around the high availability plans. We did touch in this a little bit for the SQL Server requirements but we will look to expand on this area much more.

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side Part 3 - Current Branch Upgrade Approaches

by D Walsham • 19 November 2021
Impact of Current Branch Upgrades

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side - Part 2 - Operating System Considerations

by D Walsham • 29 October 2021
Introduction

Microsoft Endpoint Manager: In-Place Upgrade or Side by Side

by D Walsham • 7 October 2021
Introduction

Endpoint Manager: Device Estate Cleanup

by D Walsham • 6 October 2021
Introduction

Modern Workplace Management: Creating Applications Common Practices - LOB vs Win32 Package

by D Walsham • 3 September 2021
Introduction

Modern Workplace Management: Build Endpoints Without SCCM Part 6

by D Walsham • 12 August 2021
All the parts of the series we went into great detail about how we analyse an end to end solution and how we would design a solution in which would allow us to build endpoints without SCCM being a dependency. Whilst we did this, there is another scenario which we have not touched on yet, which is the hybrid scenarios. In a perfect world ideally you would have your Azure Active Directory within the cloud, every machine meets the recommended requirements for Windows 10, everything is imported into Intune/Autopilot and everyone is happy. But we know this isn't realistic in all cases. Many organisations cannot just simply up and go from on-premise into the cloud therefore the checkpoint here is of course getting into hybrid solutions such as; Co-Management Between Intune and SCCM Hybrid AD with Azure AD and On-Premise AD syncing together These things can play a very interesting part in how you would tackle this if you envisage the next step in the blueprint is to be in a position in which you can build and manage endpoints soley within Intune. With this final part of the series we will go in-depth in how the common hybrid setups look like and how we go about moving into the next step of being able to manage and build devices without SCCM.

Modern Workplace Management: Build Endpoints without SCCM Part 5

by D Walsham • 29 July 2021
In continuation from the previous part where we had discussed how we create the "on site" piece of the solution, this was the part which would allow us to get our endpoints into a state in which they would essentially be ready to go through the Autopilot process. Which leaves our next piece of the puzzle, to begin the configuration of the actual backend side that resides within our Endpoint Management console. And you will see how everything ties up together to satisfy the full end to end process of getting an unknown (or known) device to proceed thorough the whole workflow to be finally managed by Intune without the aid of SCCM taking part in any of the prerequisites or preparation at hand.

Modern Workplace Management: Build Endpoints without SCCM Part 4

by D Walsham • 15 July 2021
In this part we are now going to look into the technical step by step points on how we put everything together. In the previous part we spoke about the structure of how we would asses whether a machine was actually ready to be built with Autopilot or not with a build checklist process which would step through all areas which would cover an endpoints eligibility. Now with everything planned out we finally want to step into making things reality by putting everything together.

Endpoint Manager: Find machines not in Autopilot

by D Walsham • 2 July 2021
When it comes to managing your endpoints in endpoint manager, one of the things you may be looking to do is to get all of your Intune registered machines to also be enrolled as Autopilot devices. Now we can of course just have the deployment profile deployed to all machines and then hit the "Convert targeted machines to autopilot" but this might not necessarily be feasible for every client. We may want to perform some due diligence first so we can at least understand what devices in Intune are not in Autopilot.

Modern Workplace Management - Build Endpoints without SCCM Part 3

by D Walsham • 24 June 2021
So just to recap on the previous part of this series we covered a base scenario of where we would utilise other products as a dependency to provision or even pre-provision endpoints before getting them to a state where they can be auto-enrolled and then into Autopilot - in this case this would be around SCCM. We also detailed in a diagram form of where we ideally want to be, and that's to not have any dependency when it comes to the provisioning of the endpoints where its also soley performed within Intune. This part we will be going into the nitty gritty of how this works.
Show More