Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Shotec IT 3 posts 83 karma points
    Jun 05, 2018 @ 11:01
    Shotec IT
    0

    Upgrade from SQL CE to SQL Express

    I've been running an Umbraco site with SQL CE database on a shared hosting and it's quite slow. Recently, I've purchased a Windows Server 2016 VPS on which I want to run this site and eventually other Umbraco sites a swell. The site is copied to the server but I'm getting the cannot connect to database error. I figured it might be a good opportunity to upgrade from SQL CE to SQL Express. When I wanted to download SQL Express, it told me there is also an option to use SQL Express LocalDB. Should I go for this LocalDB option or do you recommend using a standard SQL Express installation? Will the speed of the website be significantly better with SQL Express compared to SQL CE?

  • Andy Felton 185 posts 484 karma points c-trib
    Jun 05, 2018 @ 12:12
    Andy Felton
    100

    Hi,

    I would recommend switching to SQL Express as opposed to LocalDB, as there is potential that Local DB can shut down if it doesn't receive a request for a while.

    Are you seeing the slowness on the front-end or Back-office. An Umbraco site shouldn't be using many CRUD functions from the front-end so I wouldn't expect a huge increase in front-end performance. May be worth looking at your front-end code and checking some of the Umbraco anti patterns at https://our.umbraco.org/documentation/reference/Common-Pitfalls/.

    Hope that helps Andy

  • Andy Felton 185 posts 484 karma points c-trib
    Jun 05, 2018 @ 12:15
    Andy Felton
    0

    Hi,

    One other question I note you've raised this against Umbraco V8, is this using the Umbraco V8 preview?

    Thanks Andy

  • Harry Spyrou 212 posts 604 karma points
    Jun 05, 2018 @ 13:03
    Harry Spyrou
    0

    Hello

    What's the connection string you're using like? No need to post info

  • Shotec IT 3 posts 83 karma points
    Jun 05, 2018 @ 16:47
    Shotec IT
    0

    Hi Andy and Harry,

    Thank you for your responses.

    After doing some more research, I ended up on this StackOverflow page: https://stackoverflow.com/questions/10183379/is-there-a-difference-between-sql-server-express-2012-and-localdb It turns out that the LocalDB option is not good for the server since it's not a service but rather a developer mode service. I've have used the VS extension 'SqlCeTolbox' (https://github.com/ErikEJ/SqlCeToolbox) to convert my CE database to an SQL Express database and Umbraco is running fine locally. It's definitely faster than the CE database. Leason learned; from now on I go for the SQL database by default.

    Concerning your question about the backend vs frontend speed; the slowness was mainly in the backend as well as the media load times on the frontend. I believe the dedicated server + SQL server database will solve the speed issues. Thank you for the anti-pattern link. Interesting stuff. I will definitely read it.

    I am using v7.6 of Umbraco. I accidentally posted it in the V8 section of the forum. My apologies for the mistake. I will try to move this thread to the correct section.

    Harry, I've switched the connection string to the SQL CLient equivalent and it's working fine.

    Previous connection string:

    <add name="umbracoDbDSN" connectionString="Data Source=|DataDirectory|\Umbraco.sdf;Flush Interval=1;Max Database Size=4091" providerName="System.Data.SqlServerCe.4.0" />
    

    New connection string:

    <add name="umbracoDbDSN" connectionString="server=.\SQLExpress;database=***;user id=***;password=***" providerName="System.Data.SqlClient" />
    

    Thank you both for your help. I appreciate it.

  • Andy Felton 185 posts 484 karma points c-trib
    Jun 05, 2018 @ 17:05
    Andy Felton
    0

    Hi,

    Yes I think you'll definitely see a speed improvement on the back end and possibly media!

    Glad to hear the conversion went OK.

    Thanks Andy

  • Carlos Mosqueda 242 posts 433 karma points
    Nov 20, 2018 @ 23:06
    Carlos Mosqueda
    0

    FYI, there is a really great article on how to convert the SQLCE to SQL or SQL Express DB. I tried it locally before going to production. May it is great.

    https://blog.nicolaayan.com/2018/02/how-to-migrate-sdf-database-to-sql-server/

Please Sign in or register to post replies

Write your reply to:

Draft