SQL Express 2005 Replication via Visual Basic

Question

SQL Express 2005 Replication via Visual Basic

pixeldrop

SSC Enthusiast

Points: 153
More actions
May 3, 2006 at 11:24 pm

#169259

Is there a way where you can programmatically (via visual basic) replicate two or more SQL Express databases located on multiple computers without the need of SQL Server 2005 acting as a publisher. From what i understand this is a limitation in SQL Express.
Right now i have SQL Express installed on 2 machines. I am able via vb to communicate with each database, read and write data between databases but cannot seem to figure out how to perform a simple replication.
Thanks
May 8, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeremy Saunders SSC Rookie Points: 26 More actions · Answer 1

I'm working on something like this at the moment, but am thinking about this in a different way.

In my scenario, the secondary SQL 2005 Express Edition server is just in-place for DR, so I'm using backup and restore sql scripts to achieve this.

What I am doing is running a Scheduled Task from Windows that uses the sqlcmd command to run the sql script and do a full backup of the production database, and then ftp or copy the file(s) to a common location.

Then another Scheduled Task is running on the remote/DR server to copy the files(s) locally and restore the database. Before restoring you need to take the database off-line and rollback any pending transactions by using the alter database command:

alter database databasename set offline with rollback immediate

And then of course you need to place it back on-line once the restore finishes:

alter database databasename set online

So a large assumption is that the primary server is always the primary server, and any database changes made to the remote/DR server will be overwritten on the next scheduled restore cycle.

Also in my case both SQL servers are members of the same Windows Domain and the Database Logon accounts are Windows Domain (Active Directory) accounts. If the servers are in different Domains, then there is certainly a lot more work to do. I've played around with that scenario, but haven't quite got that part of the restore script under control yet.

In my situation, the SQL Server service is running as the Local System account, which therefore does not have access to Network shares, etc, so the Scheduled Task is run as a user with these rights, and the backups and restores done using the sqlcmd command uses local devices located on the server itself.

This may seem amaturish, but it does the job for me, and provides a cheap and carefree form of DR for the customer.

I'm hoping that this information helps you and is not completely off topic to what you are trying to achieve.

Cheers,

Jeremy.

pixeldrop SSC Enthusiast Points: 153 More actions · Answer 2

Thanks for your response. There is just too much involved in order to accomplish this the way you are tackling it, at least for what i'm after. What i ended up doing is writing a bunch of code (in my front end vb app) that goes back and forth between all other SQL Express nodes and based on timestamps/IDs, and other criteria replicates the newest updates for a recordset found between all SQL Nodes. Now, i will admit this is a bit cumbersome, but i will probably never exceed more than 500 records (rows of data) in a lifetime. Replication is triggered as soon as an end user performs an update to his local SQL node; this way all updates are immediately sent over to the other nodes. I still havent fully tested this but so far it seems to work. I was really after something as simple as a replication command, but i guess Microsoft knew what they were doing when they yanked this feature out of SQL Express.

Thanks,

Yiotis

Jeremy Saunders SSC Rookie Points: 26 More actions · Answer 3

Very cool. I like your way better than mine. I guess I'm not that clever with SQL stuff.

If you feel like sharing the script, I'd love to have a play with it in my environment and see if it does a better job to achieve what I'm working on.

jeremy.saunders@au1.ibm.com

Cheers,

Jeremy.

pixeldrop SSC Enthusiast Points: 153 More actions · Answer 4

I dont mind sharing this at all; I am though still working on it and releasing it at this stage would probably not be such a great idea since its not well documented and needs lots of cleaning up and tunning. Keep in mind this is specific to my needs but i believe it can easily adapt to other databases. I will notify you as soon as i have something workable.

Thanks,

Yiotis