Is Replication for Me? Help in understanding...

I have a situation where I need to distribute a db to 'subscribers' for use during network, and preferred application downtime. Currently, we do this by employing MS-Access. We update our db on the 'subscriber' by sending a text file with the new data using FTP.

When the 'subscriber' opens their local copy of the Access db application, a macro fires off to check for any new file in the ftproot folder, and if one is detect that is newer than the last update, it truncates the existing table, and imports the text file using a predefined import specification format. The process works well enough as is. However, we were hoping to move beyond our dependency on MS-Access for a variety of reasons, so we are looking at developing windows forms apps using the new Asp.Net v2.0 technology and Sql2005 and SqlExpress.

I need some clarification on how replication works. Does it allow a 'snapshot' db to be created on a subscriber that can be used when the network is down? If not do we have alternatives? For example, I guess we could export/import to the subscriber in some manner.

Hope I've made the case clear enough for some responses. Thanks in advance for your thoughts and help. :)

Here's a status update...

I was able to replicate a db from my publisher/distributor Sql2005 server to a subscriber running SqlExpress. The db on the subscriber is avaiable when the network is down.

Now, the issue I have to overcome is deploying a windows form app to the subscriber to be able to read the db on a subscriber machine. I'm having trouble figuring out the connection string. I can setup the application to read from the actual name of a subscriber/instance of SqlExpress, in this example, I'm running the subscriber db on on a server named 'myClient', and the SqlExpress instance name is 'SQLEXPRESS', using integrated security.

Data Source=myClient\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

This allows the viewer app to connect to the local subscriber db, and fullfills 1 requirement, in that the viewer app can be used to view the mission critical info during network downtime or preferred application downtime.

However, since the connection string is 'hardwired' to the subscriber server name, I'd have to create as many different viewer apps as I have subscribers. That's not the way it should work. I really need to have this viewer app be able to connect to a default SqlExpress instance on whichever client subscriber server that needs it. Trouble is - I'm not sure how to set it up. I've tried using this connection string:

Data Source=localhost\SQLEXPRESS;Initial Catalog=SCIIS;Integrated Security=True

But, I get an error message:

An error has occurred while establishing a connection to the server. ... Named Pipes Provider, error: 40 - Could not open a connection to SQL Server.

Ah, well, that is where I am at this point. End of day, so I'll look forward seeing any advice from some folks who done this type of thing, and thanks for your help.

|||

Allright! Stayed over at work to try a couple of more times to crack the issue.

I, finally, hit upon the correct connection string to use in my application:

Data Source=127.0.0.1,1433;Initial Catalog=SCIIS;Integrated Security=True

The other piece of the puzzle was in configuring SqlExpress in the Protocols. I did have TCP/IP enabled, and looking at TCP/IP properties, on the TCP/ IP Properties tab, setup the proper IP addresses with port 1433 enabled. The trick that I missed was to blank out the TCP Dynamic Ports, and changing the IPAll area to use port 1433. Once I did that and restarted SqlExpress server, I was able to connect to the local subscriber database.

As a further test, I went to the subscriber machine and disabled network connections to simulate a network downtime scenario. I started my viewer app, and successfully connected to the local db, and was able to use the viewer app w/o any issue.

Next, obstacle... How to publish the app, so I can have it be a smart client, but that's not a Sql replication issue. So, I guess, I'll say my initial question was answered.

Stay tuned...

|||

Try to enable "Shared Memory" for your SQL server protocols to see if "localhost" will work for you. You can change this setting in SQL Server Configuration Manager.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5fff42e8-377f-4b40-b0c8-b02393f8a1af.htm

Regards,

Gary

|||

Gary,

I actually had all of the protocols enabled at the time that I tested using localhost, and it didn't seem to matter. The fix for me was to change to the IP, port, and making the change to the AllPorts section as indicated above. It's working now, and we're moving forward.

Thanks for the input, though.