Hi Guys,

Can you tell me if there is a core difference between these 2 versions with regard to recovering after a hardware failure (i.e. ungraceful/hard shutdown)

The reason I ask is I have an application that I deployed using Express (App and DB on same server)... when I shut the server down hard, pull power, the database goes into Suspect mode and all sorts of errors on startup that lead me to believe I cant recover the App after doing some troubleshooting.

However when I do deploy the App using Enterprise (again App and DB on same server) and do a hard shutdown... everything comes up fine without issue.

Any guidance much appreciated.

Dryv

Hi Guys,

Can you tell me if there is a core difference between these 2 versions with regard to recovering after a hardware failure (i.e. ungraceful/hard shutdown)

The reason I ask is I have an application that I deployed using Express (App and DB on same server)... when I shut the server down hard, pull power, the database goes into Suspect mode and all sorts of errors on startup that lead me to believe I cant recover the App after doing some troubleshooting.

However when I do deploy the App using Enterprise (again App and DB on same server) and do a hard shutdown... everything comes up fine without issue.

Any guidance much appreciated.

Dryv

You wont find anywhere documented via BOL as such what is difference in recovery between express and enterprise edition. You can also refer List of features supported in SQl Server various edition Some that I know is

1. That in enterprise database can take advanatge of Fast recovery. But this is not related to what you are seeing.

2. Yes enterprise edition has automatic corruption recovery feature in database mirroring.

For whatever reasons you are doing this dont do it. This is all I can suggest corruption can happen even in Enterprise edition with way you are pulling plugs and harbooting. I could only say that you are lucky with your approach.

wow... awesomly quick response.

The reason for doing this testing is because I have been told to simulate a hard shutdown of the server (by the Architect) to see if the Application can recover from it.. At first he wanted to deploy Express and not Enterprise simple becuase its not a big database or busy database... but the testing using express failed ... so I was then told  to install Enterprise and run the tests, which succeeded.

You mention automatic corruption feature in database mirroring but I havent got mirroring configured.... this is a standalone server.

Dryv

Hard shutdown (= pulling the power plug) works the same way in Express as it does in Enterprise... If the storage below doesn't corrupt your files due to sudden power loss SQL Server will get everything back online.

Yes, there are additional recovery features in Enterprise (like the Auto-Recovery in Mirroring and AlwaysOn AGs), but that would not help you in single server deployments. And if you are OK with the Express Edition featurewise (comparison list was shared by Shanky above) then there is really nothing more to it.

https://support.microsoft.com/en-us/kb/46091

Can you simulate the same on SQL server 2008 Express with advanced Services ?

I strongly believe it based on the hardware support that Enterprise supports including IA64, Cor

Hi PrinceLucifer

Thanks for the response, and now we are pushing towards an depth conversation! I agree with you completely there are additional features as pointed out by Shanky and I also accept that they wont help me in the single server deployment which I am constrained by. I am also absolutely fine with the features offered by Express, it works perfect and we would continue using it if it wasnt for the failing of this test. There has to be something lacking in the database engine of Express compared to Enterprise (unless its at the application level).

In terms of being lucky I have also tried to rule luck out... Let me tell you how:

- Build a VM with the App and DB (running Express)

- Get everything working, give it some clean graceful reboots to ensure everything works

- Snapshot the VM at this good clean state

- Do an ungraceful shutdown. The database straight away on coming up gets put into suspect mode and corrupted.

- Revert to Good clean Snapshot .

- Repeat 15 times.

All 15 times when using Express, results in suspect mode and corruption. All 15 times when I replace Express with Enterprise its successful. 

I'm really intrigued... there has to be a logical answer to this, I dont believe its luck.

Hi Jinu,

Thanks for the response. When you ask if I can simulate the same using Advanced Services, what does this mean my friend? I'm happy to try and simulate anything right now.

Dryv

Have you tried the same VM approach with Enterprise Edition as well? I have to admit that it has been a very long time since I used 2008 Express (it is by far out of support by now anyway...), but even in those times I was always told by MS people that the DB engine is actually the same code...

Also, have you tried doing the same with SQL 2014? (Express vs. Enterprise)

Edit: What build number of 2008 are you using btw? Do have have all service packs applied?

• Edited by PrinceLucifer 21 hours 16 minutes ago

From a crash recovery perspective, the main difference between Enterprise and lesser editions is the fast recovery feature.  Enterprise Edition will bring the database online after the redo recovery phase completes, while the undo of uncommitted transactions continues.  In contrast, Express (and Standard) will remain in "Recovering" until the entire database is in a consistent state.

This does not explain the suspect database you see with Express.  For recovery to work properly, log records must be properly hardened during commit and checkpoint, and checkpointed data records hardened as well.  I'm not aware of a difference in database editions that would result in a suspect database but I'll see if I can find out more.

Can you confirm Enterprise and Express were configured identically (e.g. no AlwaysOn)?  Can you share the SQL Server error log? 

Have you tried the same VM approach with Enterprise Edition as well? I have to admit that it has been a very long time since I used 2008 Express (it is by far out of support by now anyway...), but even in those times I was always told by MS people that the DB engine is actually the same code...

Also, have you tried doing the same with SQL 2014? (Express vs. Enterprise)

Edit: What build number of 2008 are you using btw? Do have have all service packs applied?

• Edited by PrinceLucifer Saturday, September 05, 2015 9:48 AM

 There has to be something lacking in the database engine of Express compared to Enterprise (unless its at the application level).

Yes there is but that is again not documented. Express DB engine is free and enterprise has good cost associated with it. Of course code base of express is different from that of enterprise

All 15 times when using Express, results in suspect mode and corruption. All 15 times when I replace Express with Enterprise its successful. 

I'm really intrigued... there has to be a logical answer to this, I dont believe its luck.

Sorry but question is still the same and so answer would be same as before. I agree there has to be some logical explanation but to point it out from here by just talking to you virtually seem difficult .

btw. I just tried to repro your behavior on my virtual environment using the 10.0.6000 build of SQL Server 2008 (=Service Pack 4, both Express and Enterprise) and I can't get the same behavior of either builds... Tried it about 100 times and not a single time ever got a corruption...

Make sure that your test environments are *identical* except for the installation media. I.e., automate thw lot including SQL Server install (unattended install). Re-do it all, including creating the VM, installing SQL Server (using unattend) and your tests. Do the whole process several times, and intermix express vs EE. 3 full installs and test suited with Express, 3 with EE, 3 with express, 3 with EE, etc. Express should absolutely not differ from EE regarding suspecting the database for an hard shutdown. Hardware issues (like disk subsystem not adhering to write-ordering can cause this), but not different editions.

Yes this will take time...