How to use MSDE(SQL Server) as GBPVR Database

[KingArgyle]

Note: Sub has nothing to do with the process I'm about to describe. If you try this method, and mess up your GBPVR application, do not ask him for help. I'll try to answer questions regarding this. Sub may or may not ever "officially" support MSDE or Sql Server as a database for GBPVR. The initial notion for doing this came from a Wishlist item, and Sub's response that initially GBPVR was developed using MSDE as the database, and that the SQL used was pretty generic.

Well, I was curious if the current version of GBPVR could be made to use a SQL Server database such as MSDE. After much trial and error, and many reinstalls of GBPVR on my development machine I think I have it working. I've checked all the logs, and they are all saying they are using the SQL Server connection string, and my access database last update time stamp has not changed (even though the LDB for it is getting updated). Not to sure why that is getting updated, but it is.

Regardless, from some brief testing, a couple of items I have noticed:

1. Data Access seems much quicker. Noticeably so with the Web Admin, and building the initial TV Guide page.

2. TV Guide in the main GBPVR application also populates much quicker.

3. Refreshed my EPG data from Zap2It, and refresh time took half the time it normally does.

Now, to get this to work is not for the faint of heart. You have to follow certain steps. I'm not going to go into great detail but here is what needs to be done.

1. Install MSDE either from your Office XP CD, or you can download a copy at http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1

2. You'll also need a Manager for MSDE as it doesn't come with one. A very basic one that will let you add new users to the system can be found at: http://www.whitebearconsulting.com/Utilities.htm

3. After MSDE is installed, reboot your system, or start the MSSQL service in your services panel.

4. Run the MSDE Manager and a user named gbpvr as a SQL Server user. I just set the password to the same as the user name. Also, I gave this user full authority to the SQL Server system.

5. Launch Access 2000 (or newer), and load the GBPVR database.

6. From the menu, select Tools -> Database Utilities -> Upsizing Wizard. Select Create New Database, when prompted give it the name of the Database, the gbpvr username you created, and the password. Also, name the database GBPVR (the default is GBPVRSQL). Follow the prompts, and when it asks, select the No Application change radio button. The wizard will migrate all tables, and data to SQL Server.

Next you'll need to modify your GBPVR config.xml, so that it points to the new database location.

Important: If the GBPVR Recording Service is currently running, Stop the service.

1. Find the DataBaseConnectionString element, and change it to something like this:
Provider=sqloledb;Data Source=(local);User Id=gbpvr;Password=gbpvr;Initial Catalog=gbpvr;

Change the user Id, and Password, to the user you created, and the Initial Catalog to the database you created using the Upsizing Wizard.

2. Next, shutdown and reboot. You could probably just stop the GBPVR Recording Service, but have found that the best way is to just shutdown and reboot the system.

3. Once the System reboots, everything should be as it was before, except you will now be using MSDE as the database. You can check to see if GBPVR is using your new database setting by checking the GBPVRTray.exe.log file. Also, when you launch the Config.exe, and the GBPVRRecording Service, their logs should also specify have the new connection string displayed.

That is it. A couple of notes. Do not remove the MDB file. If you do so, Windows will try and repair the GBPVR installation. Trust me, this caused me to have to uninstall and reinstall my GBPVR application many times.

Why would you want to switch?

1. Mainly for Speed. SQL Server/MSDE is much quicker than Access at doing queries. It is optimized for speed.

2. Enhanced querying capability for future plugin development. Similar steps for migrating Jorm's Zap2It meta data database to MSDE could be undertaken. With that database loaded there as well, it would allow for fairly simple cross database queries to be undertaken, to provide extra information for the GBPVR application and plugins.

3. Reliablity and data integrity. Access has always been buggy when it comes to database integrity. Loss of data, and index corruption were very common in the past.

4. It gives the end users a choice, of where to have it installed. If you already have a MSDE application on your system, now you can store and manage the GBPVR database there as well.

Why did I do this?

A. Mainly for my own curiosity. I currently only have this combination setup on my development machine. I had originally tried to get MySQL running, and actually came very close, except the OLEDB drivers are very buggy and I kept being prompted for username and password everytime GBPVR would access the database. Plus I wanted to see what type of speed difference, if any would occur.

Do any other PVR applications give you this option?

A. Not that I'm aware of. I've used BTV in the past, and most of their information is stored in XML files, which is good and bad. I have no idea what GAM, MediaPortal, or any other PVR software uses as their database. This may make GBPVR unique in this aspect.

[Huw]

if you have an active ldb file, then GBPVR is still using the access database

[Jeff]

Or a plug-in is. For example, in the VideoArchive I gostriaght to the GBPVR database to cancel reoccring pending recordings since the built-in funciton still doesn;t seem to work for me. My Archive Update utility also goes straight to the GBPVR DB. I should probably read the DB string from the config.xml file instead.

[KingArgyle]

I would suspect that the modification date and time for the MDB would be changed whenever an update occured to the database. I'm still going to do somoe more testing, it was 2:30 in the morning when I finally got it too boot without errors, so didn't do extensive testing.

[Huw]

it may not be updating the db, so it's date would not change, but if you have an ldb file then something is opening the database

[KingArgyle]

Actually, I deleted some pending recordings, so that should have updated the database. Easiest way to check is to do a query between the two databases and see if the pending recordings I deleted are still in the database or not.

Update: Well, I know that it is reading the MSDE database, and updating that database. The reason is that I cleared the Access MDB database of all program records, and all scheduled recordings, and left the records intack in MSDE. Thus if it was indeed trying to get anything from the Access database I shouldn't see any guide information at all. All guide information was returned correctly, and all scheduled recordings were returned as well.

I don't doubt that something is accessing the Access database. I know Sub said he put one specific access function in, to flush the cache every once in a while, so that might be what is accessing the MDB. As far as I can tell, though all of the other parts of the system seem to be using the MSDE database.

Downloading Guide data seems to work fine. Scheduling a Recording gives an InterOp error, through the Web Admin.

Anyway, with a few slight tweaks, I think Sub could make it so that either Access or MSDE could be used as the database for GBPVR. Access would be the default and it would be up to the user to set up MSDE. The speed difference would be worth it to me to have it run off MSDE.

[Huw]

Quote[/b] ]I don't doubt that something is accessing the Access database.

Then you shouldn't have an ldb file. The ldb file is only created when you open your access db, it is deleted when you close it, so if the date of your ldb is changing, then something is definately opening your access db, you can't get an ldb file any other way.

[KingArgyle]

Sub verified that he has some access specific code in 0.23.8, and that he removed this from 0.23.11. I haven't installed or tested this with version 0.23.11 to see if the .ldb file is still there.

Hopefully, I'll get time to night. Regardless, the only place I had any problems with the app using MSDE is when scheduling a recording, and I believe that this has to do with the access specific code that is in place in .23.8. I'm also going to disable all plugins, and turn them on one by one (I just have the standard plugins that come with GBPVR). If I turn one on and all of a sudden the LDB file returns, then I know that they are accessing it directly instead of getting the Database from the config.xml file.

[Jeff]

I tried the procedure but obviously messed up on a set along the way. When I look in the recording service log file I see the error "Login failed for user 'gbpvr'. Reason: Not associated with a trusted SQL Server connection."

I've tried using MSDE Manager and I can;t see where I set whatever it is I need to set.

[KingArgyle]

Make sure that when you create a new user using MSDE Manager or another utility that you grant that user all rights. Otherwise, you have to grant access to the gbpvr database that you created.

If you are using the WhitebearConsulting manager, this is done under the User Login menu item. select Edit a User, and then grant the all the roles to the gbpvr signon.

After you do this, try logging in to the MSDE database with the user id you created. You can also setup an ODBC datasource for testing, and test the connection with the userid and password that you created for gbpvr. That should at least let you know if your id is being seen correctly.

One other thing, is to make sure the default login for the MSDE database is set to SQL Enterprise security instead of Windows Security. If it is set to Window Security, you then need to make sure you add a Windows Signon for that user to your system.

I'm going to be going through these steps again tonight, so I'll check my gbpvr user id I have setup on MSDE and see if I missed any steps.