Guest post by James Studdart from The Cynical Developer.
A bit of background
After interviewing Paul on my podcast, The Cynical Developer, and following the Living Coding series on YouTube, which walks you through taking a free html5 template and converting it to a working Umbraco website, I took the plunge and built a new version of the podcast website.
The original Cynical Developer website was a quick MVP to get going, it was built with WordPress. This served its purpose but I felt I wanted something more, and Umbraco appeared to be the answer.
I didn’t convert an existing template, but hand rolled a new design for the website.
For some reason when I installed Umbraco locally I did not follow Paul’s advice and set it up to use a full version of SQL Server.
Umbraco was running slowly
The build went well, and I had little trouble building the site, I found speed to be a small problem, but it wasn’t unbearable. As I hadn’t experienced this new Umbraco before I just took the slowness to just be a down point to using Umbraco.
So I released the website to the live server, where it sat fine for around 24hrs, and then boom! Traffic picked up and it ground the server to a halt. At this point I will point out this is based in Umbraco version 7.5.6 assembly: 1.0.6185.24083.
When I was requesting a basic page the initial request to the server rocketed the CPU up to 60% usage and sometimes more. These pages didn’t do a whole lot; they just got their content and rendered it. When you first go to a page in Umbraco there is a delay, while the system builds the page and caches it, after this the load time is normally faster, but I was waiting nearly 30seconds for the initial load, and a similar time for subsequent requests too.
At one point the website completely froze IIS for me, which resulted in a full IIS restart, which was painful, to say the least.
Maybe it will run faster with full SQL?
The only thing I could think it could be was maybe that the reading of the SQL CE file was just poor. I decided I would migrate to 'Full' Sql Server, now how was I going to do that?
I would imagine like a lot of you out there, you’re all using an up to date version of SSMS (Sql Server Management Studio) and Visual Studio too, which doesn’t seem to offer any support for SQL CE. I turned to the internet for help, specifically Google, with a search along the lines of “SQL CE what the hell do I do when SSMS doesn’t support it anymore!?” I could be paraphrasing there.
Converting the database to full SQL
I found a great little FREE application called CompactView, we all love a bit of FREE. The application gives you very basic CRUD access to an SDF file.
The best feature that the application provides which would help with the migration, is the ability EXPORT the database to a Sql Script, you can export just the schema, just the data or both into a single script. I did find it tried to build a few foreign key constraints which didn’t seem to exist; I manually removed those from the script.
I then simply created a new database on my server, and ran the script against, and Boom! I had a fully populated database ready to go.
Next was changing Umbraco over to use 'Full' MsSql, I thought this was going to be a nightmare. I have migrated other systems in the past, from different database systems and its always involved extra dll’s, compromise and sometimes even rebuilds and code changes.
Umbraco did not need any messing about and after creating the database, as described above I was over and working on 'Full' Sql within a minute or so.
“How do you do it!?” I hear you all cry, well very, very, very easily…
The magic!
Open up the web.config (I did this on the staging version first, I’m not completely insane) and locate the connection strings
The string you want to replace is this one:
<add name="umbracoDbDSN" connectionString="Data Source=|DataDirectory|\Umbraco.sdf;Flush Interval=1;" providerName="System.Data.SqlServerCe.4.0" />
Replace it with:
<add name="umbracoDbDSN" connectionString="server={your instance};database={user database name};user id={UserMcUsername};password={PasswordMcPasswordFace}" providerName="System.Data.SqlClient" />
Restart your AppPool, and website in IIS... and you're almost done.
Check your website, hello! It's now running on 'Full' Sql, you have one last job to do. I found that the site loaded faster and the admin panel was a dream to work with, whereas before it would become painfully slow and unresponsive.
Now head to your website's directory and head into App_Data folder. Locate the SQL CE file, Umbraco.sdf, back it up just in case you need it, or if you're feeling rebellious and just wanna live on the edge, select the SDF file, hold Shift and smack that Delete key and then hit Yes!
Get in there!
You my dear friend are now SQL CE free, and implementing this is a lot quicker and easier than reading my rambling writings. Please enjoy your new faster and lag free website and tell your friends about CodeShare.co.uk right now!
If you liked this post, I don't normally write blog posts, then please do head over to the Podcast and give it a listen.
Lastly, I would like to thank Paul for inviting me to write this article, I hope you found some benefit from it and if now, maybe it waste that last 10 minutes you had at work today!
Until next time, thanks!