Is that SEO-specific enough, do you think? Heh. Anyway, here's the situation. I started this blog using GraffitiCMS about a year ago, previously having used static web pages created with CityDesk. All my sites are hosted on GoDaddy: I went with a Deluxe hosting option which means I can host pretty much as many sites as I want to in the same single folder tree.
At the time — can't remember why — I decided to stick with the default VistaDB as the database instead of opting to go to SQL Server. Throughout the year, things have been good, no issues whatsoever, so my decision seemed sane. Until about two weeks ago that is, when everything went to hell in a handcart in a hurry. Essentially, for a whole week, this blog went offline:
I still don't know what was up. I uploaded a blog post the day before, then, blam, November 15th was a black hole that lasted 6 days. As detailed here, I tried to fix it once I was back home from PDC — thought I'd had, but ever since it's been acting weird. Every now and then, for no discernable reason I can see, some process (obviously an ASP.NET IIS process) locks some section of the VistaDB database file and it's curtains for the blog until the process resets, several hours later. I'm guessing that it's some bug in GraffitiCMS and one request is hanging, causing everything else to hang. Or it's because something is timing out from taking too long. At any rate the log slowly starts to fill up with errors, since every three minutes or so some process kicks off to clean out deleted records:
Plus the site has suddenly been acting as if it's very tired. It's just been plain slow. Just nasty to use (I was counting something like 7 seconds for a blog post to come up).
Finally yesterday I'd had enough. The site locked up again (for about 5 hours this time), so it was just time to move to SQL Server. At least with that I have some diagnostic tools and the like to find out what's going on.
With my GoDaddy hosting plan I have the ability to have up to 2 SQL Server databases. Not many, Benny, but then again I only need one. First problem is that, by default, GoDaddy does not expose your SQL Server database for remote access. They provide a nice web-based tool to manage your database, but it seemed a little limiting to me: I wanted to use SQL Server Management Studio to manage my database. Plus, the only way I was going to be able to get all my data into the database was to do it via a remote connection.
So I fired off an email to support asking for the right to access my database remotely. I got a reply back after three hours saying that I should delete my current databases (after backing them up of course). Also they had to move my whole domain onto another hosting server in order to allow remote access to the database. No problem (I only had a test database to try out the standard GoDaddy tools and this was soon dropped), so I quickly replied that my domain was ready to be moved. Four hours later, I got a reply saying, in effect, that "due to its complex nature, your issue has been relayed to our Advanced Technical Support Team". OK, I let support take their course and went to bed.
This morning, I had a slew of messages saying that the move had been successful and that I could now access SQL Server remotely. Amazingly the whole site was much more responsive than before: I reckon the previous hosting server was a bit overloaded (perhaps this was also part of my problem?). This evening, then, was the time for the Big Conversion.
1. I logged into my hosting account on GoDaddy and created a new database. On the database creation page was a radio button to state whether this database could be connected to remotely or not, something that had been missing before:
I made sure that the button was set to Yes. 5 minutes or so later, the database had been created.
2. I fired up SQL Server Management Studio (SSMS) 2008 and connected to the SQL Server URL given by the database configuration page, providing the user name and password I'd set up for the database. It connected just fine.
3. However, when I tried to open the Databases node in the Object Explorer, I got SQL Server error 916. My credentials were not able to access another database I'd never heard of. I went back to the SSMS server login dialog, and added my database name. Same error. I googled for the answer and found this article by Clay Burt that described the same issue. It made reference to this Microsoft Connect post about how to fix the problem. Once I did this and refreshed, I saw a huge long list of the databases on the server, of which mine was one. (Of course, the only database I was able to open was my own.)
4. I opened my database in SSMS and then ran the Graffiti_SQL_Schema.sql script (it's in the top level \Data folder of the GraffitiCMS install). This created the database schema that Graffiti uses. Then it was time for the data.
5. I copied my whole GraffitiCMS site from GoDaddy onto my local disk. I normally do this for backups every couple of days anyway, but this time I wanted to make sure I had the latest, most-up-to-date Vista VDB3 database file. I was going to be using the DataMover utility to copy the data over to SQL Server. I set an "under repair" default.html file in the root folder so that visitors would know something was going on.
6. Now it was time for DataMover. This utility is found in the \Data\Utility\Migrations folder of the GraffitiCMS install. It requires two databases: the source and the destination. For the source, I browsed to the folder containing the VDB3 file I'd just downloaded, and for the target, I entered in the .NET connection string that pointed to the GoDaddy SQL Server instance that was hosting my database. (GoDaddy provide this in their database control panel. Open the database control panel, click on the pencil icon next to your database, then click on Configuration. Find the one that says .NET, it's just a case of selecting it all, copy-and-pasting it, and then overwriting the dummy password with the real one). I clicked Copy Data and then started to write this blog post about how I did it all.
7. Once all the data had been copied to the SQL Server database, it was time to fix the web.config file that I'd downloaded. First of all, I located the current connection string. It looked like this for me:
<add connectionstring="Data Source=|DATADIRECTORY|\boyetblog.vdb3" name="Graffiti"></add>
I changed it to look like this (with certain values changed to protect the innocent, namely, me):
<add connectionstring="Data Source=boyetblog.db.999999999.hostedresource.com;Initial Catalog=BoyetBlog;Persist Security Info=True;User ID=userid;Password=password;" name="Graffiti"></add>
Now I had to change the data provider. Further down web.config, there's a line that said:
<add key="DataBuddy::Provider" value="DataBuddy.VistaDBProvider, DataBuddy"></add>
And I changed it to look like this:
<add key="DataBuddy::Provider" value="DataBuddy.SQL2K5DataProvider, DataBuddy"></add>
I then uploaded the changed web.config file to the root of my blog site.
8. Time to test. I played around with as many features of the site that I could, especially the admin pages, including reporting. All seemed to work just fine. In fact, better than that, it is rip-roaringly fast. Having the database engine on a separate machine from the hosted web server is just slick like skates on ice.
Now playing:
Level 42 - World Machine
(from World Machine)
Loading links to posts on similar topics...
6 Responses
#1 Dew Drop – December 1, 2009 | Alvin Ashcraft's Morning Dew said...
01-Dec-09 6:06 AMPingback from Dew Drop – December 1, 2009 | Alvin Ashcraft's Morning Dew
#2 Jason Short said...
01-Dec-09 3:47 PMThat's too bad you had problems, but it really is not an issue with VistaDB (your article makes it sounds like it our fault somehow). Graffiti has implementation issues and is not being maintained. There are product errors with Graffiti that Teligent won't address. See their forums for impassioned cries from us and others for help. They had no response.
I would say the better article here would have been you moving from Graffiti to something else (Like Dotnet BlogEngine.net). That's what we did, and we still use VistaDB on our blog with no problem.
Graffiti did have a lot of nice features, I really liked it. But the lack of support, lack of communication, and a general sense of not caring is why we (and many others) moved away from it.
#3 julian m bucknall said...
01-Dec-09 5:43 PMJason: Nope, didn't mean to imply that at all. As I said right off the bat, I've been perfectly happy with using VistaDB as the database engine in GraffitiCMS on GoDaddy for a solid year. No problems, no issues, just software serving up content from VistaDB's database. In fact, I really liked having VistaDB as the engine, since doing backups was really easy (I just FTPed the VDB3 file to a local folder).
Now what happened mid-November, I do not know. Nothing about GraffitiCMS has changed since version 1.2 in February (?), as you know. So it must be something else:
The server that GoDaddy hosted this site on has become overloaded. Maybe too many sites, maybe too many badly-written ASP.NET apps hitting the IIS instance, maybe the hardware is flaky and needs upgrading, I don't know. I will say that, when GoDaddy moved me to another server in preparation so that I could access SQL Server remotely, the site immediately sped up even before I made any changes. Also, if I ran GraffitiCMS locally using the same database, no issues.
Part of the issue I think is that, for whatever reason, GraffitiCMS crashed during an update on November 14/15. There was some kind of referential integrity violation afterwards, which was the point of my previous post where I grumbled about Telligent not providing any kind of database repair facility, despite VistaDB providing one out of the box. (I mean, I totally agree with you, that's just bloody silly.)
Er, well, that's it.
I really think in this case that it's infrastructure related.
Also, I didn't want to move to another blogging engine (I'd already done some research on WordPress and BlogEngine.NET, but neither attracted me). I just like GraffitiCMS, especially the templating system. Given that in 10 days' time it will be open source, I'll be able to have much more control over it than I've had in the past, but then again, I haven't needed that control for the last year anyway.
Cheers, Julian
#4 trincas said...
17-May-10 2:51 AMCan you tell me how to open and edit .vdb3 database? I have tried a lot but failed. I want to modify the file but dunno how to do it. Many thanks in advance.
#5 julian m bucknall said...
17-May-10 8:15 AMtrincas: VistaDB is not a standalone database like, say, SQL Server or MS Access, it's a library that implements an embedded database for .NET applications. Telligent used it as the default database for GraffitiCMS for those very same reasons: .NET and embedded. Unfortunately they didn't do a great job and then decided to open source the product before making amends.
So the only way to 'edit' a VDB3 database is to get VIstaDB the product, and use their example database editor program that comes with the library.
http://www.vistadb.net/
Cheers, Julian
#6 asava samuel said...
10-Mar-13 5:16 AMJulian
Here is an ORM that works with VistaDB
www.kellermansoftware.com/.../p-47-net-data-a
Leave a response
Note: some MarkDown is allowed, but HTML is not. Expand to show what's available.
_emphasis_**strong**[text](url)`IEnumerable`* an item1. an item> Now is the time...Preview of response