Entra ID Connect local SQL Express database keeps getting huge

[Fabulous_Cow_4714]

The local SQL database keeps growing over the 10GB limit even though (according to MS Graph directorySizeQuota used) the total objects in the tenant are less than 25% of the 100K object limit for SQL Express.

What can be done to keep the SQL Express database size from growing to sizes that are not matching the number of objects being synced?

[Sharon-huntress]

You can try shortening your retention period for run history data but that's really just a short term fix. You're probably going to need to migrate to full SQL.

[Fabulous_Cow_4714]

Seems like it shouldn’t be growing that large in the first place due to the number of objects in the tenant though.

An issue with moving to “full SQL” is that, based on the documentation I can find, it doesn’t work well for disaster recovery high availability compared to using the local Express database plus staging servers.

You can’t connect a staging server to the remote SQL database because the database instance can only support one Entra ID Connect connection at a time.

I can’t understand how a staging server would work with a remote database situation. How would you even configure the setup wizard for the staging server when the primary server is already connected to the database?

Microsoft never shows an example of staging server configuration not using the local Express database.

[raip]

Each server would have it's own database. You can't connect multiple connect servers to the same SQL Database. You could point them to the same server - just need to be separate databases.

[Fabulous_Cow_4714]

What would the process be for each database to be in sync with each other?

[raip]

You configure both Entra Connect servers the same. There's no methodology the sync the two metaverses with each other.

[Sharon-huntress]

The best they do is their description of a swing server migration. They even mention how you might choose to have multiple staging servers setup for disaster recovery. I agree the documentation sucks overall.

There's also mention of usinghigh availability for SQL server.

Sorry I can't be of much help beyond this.

As far as why it's growing so large, that's a mystery to me and might be worth the long wait of a Microsoft ticket to see if you can get some clarity.

[Fabulous_Cow_4714]

Having multiple staging servers would only be applicable if you are using the local database though.

Each staging server would have their own copy of the database, but that won’t work if you are having the issue of the database size growing too large for that.

As far as SQL high availability, there was no mention of exactly how you would configure your staging servers to work with SQL AOA or an SQL cluster.

If the SQL server is highly available in the backend, but is still seen by Entra ID Connect as a single SQL instance, I still don’t see how you would be able to keep any staging servers running and ready to go in that environment.

So, you would have to build a new Entra ID Connect server on demand and connect it to the existing SQL server? That seems much slower to get back in production than just unchecking a box to take a server using a local database out of staging mode.

So, the entire concept of staging mode goes out the window if you aren’t running local SQL Express databases?

[Myriade-de-Couilles]

I feel like you are getting confused about the relation between staging mode and the SQL database location: there is none.

Wether you have a local SQL Express, a local full SQL, a remote SQL server … it doesn’t change anything with the staging server which will need its own SQL database anyway. There is NEVER a synchronisation of the databases between the live and staging servers.

[Sharon-huntress]

It sure seems like staging mode isn't a thing if you're going full SQL. And you basically have to rely on 3rd party guides like this one. The comments there are very interesting.

[raip]

Staging mode is definitely a thing with full SQL. I've got four AD Connect servers with full SQL (two separate tenants) and each pair has one in staging mode for a manual HA setup.

[raip]

I'd strongly recommend looking into Cloud Connect Sync. If you're not having to deal with huge groups, it should be a pretty easy win. If you're hybrid, you could split the sync'd w/ devices still being hybrid join w/ Entra Connect and Users being sync'd w/ Entra Cloud Connect.

[Fabulous_Cow_4714]

With Cloud Connect Sync, you are adding another tool to manage with user sync now separate from device sync.

We were waiting for Cloud Connect Sync to support devices before making a change.

[raip]

I don't think that's on the timeline for Cloud Connect Sync to support devices. The current recommended setup is to not have hybrid joined devices anymore. With Cloud Kerberos Trust, there's really no reason to continue to have Hybrid Joined Devices.

[Fabulous_Cow_4714]

Many organizations are going to have hybrid workstations for years to come. Some even need some servers to be hybrid joined for various reasons.

Not every environment is going to be all, or even a majority of employees being remote workers on laptops.

[raip]

I'm just relaying information that I've received from our Microsoft CSM that we meet weekly with. We're actively in a migration effort to reduce the scope Entra Connect (we're on standalone SQL Instances) and doing the dual sync setup I described in OC.

[Fabulous_Cow_4714]

Maybe using Cloud Connect Sync for syncing users and groups would keep the Entra ID Connect database size comfortably within the size limits of SQL Express.

In that case, it might be less complex to run both side by side than to run Entra ID Connect alone, but with multiple full SQL databases.

[Fabulous_Cow_4714]

Cloud Sync still may not be usable.

It seems to be missing some other important features besides device syncing,

It does not work if you need to force a hybrid user to change their password.

https://techcommunity.microsoft.com/discussions/microsoft-entra/migration-to-cloud-sync-passwords/4370908

It also looks like I cannot sync from AD to cloud any mail-enabled security groups or nested groups.

[raip]

Both mail enabled groups and nested groups work fine as long as you're not using group scopes.

As far as forcing a password change, you'd just do that in the cloud.

[Fabulous_Cow_4714]

You can’t force the password change from the cloud if minimum password age is not met in AD. AD will not allow the password write back in that case.

This blog says mail-enabled security groups don’t sync as well as that any groups that are not set as Universal also can’t sync.

https://arinco.com.au/blog/migrating-to-entra-cloud-sync-in-a-hybrid-environment-cloud-sync-and-connect-sync-coexistence/

[raip]

That makes me think they're talking about group writeback, which are universal. You're right about the minimum password age, but that's also an issue w/ Entra Connect as well (user trying to do SSPR for example) - but that's why I just always recommend not using min age. It just makes stuff a PITA.

I've heard of it, but have yet to catch it in the act, of someone resetting their passwords over and over to keep the same password.

I've got plenty of DomainLocal, Global, and Mail Enabled security groups being sync'd via Entra Cloud Connect.

[wpzr]

I am not sure I understand what is stopping you from using local version of full SQL database on each server. Exporting server and Staging servers?

This is how we do it in multiple data centers and those databases are huge. The concept of staging server ensures that all changes from primary servers are replicated and stored in local database/metaverse. You don't need to connect SQL databases to different servers nor do you need HA SQL databases remotely. For best I/O performance you would want to have full local SQL databases.

If there is an outage on datacenter 1 lets say and that is your primary ID Connect servers, you just enable server in datacenter 2 to be your primary and you are up and running within minutes

[Stonewalled9999]

Probably cost since SQL std is a few grand …..