I am considering using a local database for a desktop application, instead of relying on remote connections. This will require replication I suppose to sync up table changes etc. What are my options when using Sql express on the client end and Sql server (standard edition) ?
After looking at this
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Now, Im wondering how do I get local changes merged back into the primary database ?
I am looking for a way to replicate databases from an environment using SQL Server 2000 sp4 to machines using either 2000 sp4 or 2005 sp2. The size of the dbs (+3 gb) warrant a transactional model, but the developers opted to not create primary keys for any of their tables and PKs are required for transactional publications (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1627873&SiteID=17&pageid=0#1614178). Creating PKs are, most likely, not an option given the time constraints of the project.
I'm also aware of the fact that merge replication does not work from SQL Server 2000 to SQL Server 2005 (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=499634&SiteID=1).
I'm looking for alternative solutions to this problem, regardless of whether it's replication or not. Log shipping was suggested at one point, but I want secondary confirmation on this. Any help is appreciated. Thanks.
UPDATE: Log shipping in SQL Server 2000 is supported in Enterprise and Developer edition. The source server is using 2000 Standard. UPDATE 2: Log shipping in SQL Server Standard is *possible* but unsupported http://sql-server-performance.com/Community/forums/t/17798.aspx?PageIndex=1. It looks like the original poster was still having problems by the end of this thread.
I would get from scripting the replication out from SQL Server itself. However, there are a couple of things that are not showing up in the script,Adding the option to IncludeUninstallDistributor or UninstallDistributor doesn't appear to add any code to actually do that. i.e. the below code is never added.
/****** Uninstalling the server as a Distributor. Script Date: 10/1/2015 4:34:13 PM ******/ use master exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1 GO Also, adding the reverse for the create script generation, IncludeInstallDistributor or InstallDistributor, does not add the below code. use master exec sp_adddistributor @distributor = N'xxxxxx', @password = N'' GO
The purpose of the script is simply to script out all drop/create scripts for all publications and subscriptions, run the drop script, restore over the databases using backups of non-replicated databases, run the create replication script, and have it re-sync.
Why did Microsoft release a replacement for MSDE called SQL 2005 Express with Advanced Options without the SQL Agent? I bought into the MSDE and got rid of the other databases we supported now we have to go a create an equivilent to the SQL Agent for a) scheduled jobs b) backup automation. c) etc.
Does anybody really want a database that does not have these built in? I understand I can do these myself but this seems to defy logic to me?
I have a VB.net app that access a SQL Express database. I have transactional repliaction set up on a SQL 2000 database (the publisher) and a pull subscription from the VB.net app. I use RMO in the VB app to connect to the publisher. My problem is I am getting some strange behaviour as follows
- if I run the app and invoke the pull subscription it works fine. If I then close my app and go back in, I can access my data without any problem
- If I run the app and try to access data in my SQL Express database it works fine. I can then close the app, reopen it and run the pull subscription it works fine
however.......
- if I run the app, invoke the pull subscription (which runs fine), and then try to access data in my local SQL Express database without firstly closing and reopening the app, I get a login error
- if I run the app, try to access data in my local SQL Express database (which works fine), and then try to run the pull subscription I get a "the process cannot acces the file as it is being used by another process" error. In this case I need to restart the SQL Express service to be able to run replication again.
I get exactly the same behaviour when I use the Windows Sync tool (with my app open at the same time) instead of my RMO code to replicate the data.
I am using standard ADO.Net 2 code to access my SQL Express data in the app and closing all connections etc
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Q1: Does Sql Server 2005 Express support Web/Internet to other SQL Server 2005 Express Clients or does it have to Synch across the internet to a fully installed setup SQL Server 2005 with IIS?
Q2: Does SQL Server 2005 Express support Direct Replication between other SQL Server 2005 Express clients?
I know SQL Express supports replication as a subscriber, but a company I may be going to has a bunch of SQL 7 servers that will be upgraded to SQL 2005. They are all small databases <200MB and I see no need for full versions of SQL. The only issue is they use replication to and from these boxes to corp every night. So from corp to the stores I would use replication. From the stores to corp I was thinking of using SSIS to pull the information or maybe a job to export the data and transfer it to corp. Was looking for other ideas. The communication lines for most part are high speed but they do have unexpected outages in different areas of the country so that is a consideration.
Raymond Laubert MCITP:Administration, MCT http://www.rd-webhosting.com Home Based Business Library and Hosting
I am developing a web site that contains a SQL Express database. When done, I want to FTP the entire site to server running in a DMZ. All changes are done on the inside, and every evening it will synchronize by copying everything, including the database to the DMZ web server. Am I going to have issues with the SQL Express database in doing this? TIA --> Jon
Hello: I tried to set up a pull transctional replication on SQL Express 2005. The publisher is SQL 2000 which has tables that have primary keys. However, only tables I see in my SQL express (Subscriber) are the system tables. What did I do wrong?
I can't find the right answer on the web. Thanks GCP
does any know of any plans to support publications in Sql 2005 Express? I want to start moving away from MSDE but we use replication (one MSDE database is the publisher and several client PC's use MSDE as the subscribers.).
Hi All I am trying to sync sql server file on two separate computers running SQL Server 2005 Express. I am writing an application (VB.NET) and I am trying to update the other computers when data is changed. Is it possible?
I have tried to setup Web replication following the instructions in the articles in SQL Books Online, using RMO. I am able to create the subscription, but when I try to sync it I get the following error.
The process could not create file 'C:DOCUME~1lsimpsonLOCALS~1TempHNTBW115D$B_borings_BoringsAll_C:BORINGSBORINGDB.MDF' due to OS error 123.
Is anyone familiar with this error? If I can solve it, I think the replication will work.
Web Replication Enabled IIS Configured as documented, except DB server is an AD Domain Controller so can't assign Local User to Application Pool
SQL 2005 Express
Using RMO to create subscription Using Basic Authentication of HTTPS Calling the replisapi.dll?diag works fine
When I attempt to do a web synchronization with the Publisher, I get the following error:
The Merge Agent could not connect to the URL 'https://test.abc.com/TestWebReplication/replisapi.dll' during Web synchronization. Please verify that the URL, Internet login credentials and proxy server settings are correct and that the Web server is reachable.
Does anyone have any suggestions. I am sure it has something to do with the security setup on the Publisher, but I can't think of anything else to try.
I am running a local version of SQL Server 2005 Express and SQL Server Management Studio Express Edition. I have a web app using the database and I need to migrate the app, the database and database objects to my outsourced host. What is the best way to do this, since the replication and packaging capabilities are limited. Is there a standard, simple way to do this?
Hello,I am learning SQL Server 2005 Express. I need to test replication butwhen I run New Subscription Wizard I got error: "There are nopublications to which you can subscribe, either because this serverhas no publications or because you do not have sufficient privilegesto access the publications."I see no option for publications. Does this mean that in Express Icannot run replication? If I can, how?Thank you very much/RAM/
I have a Windows Mobile aplication that uses merge replication to synchronize between sql compact and full sql Enterprise edition and It works great. Instantiating the SqlCeReplication object setting it's properties and calling Synchronize on it is pretty simple assuming everything is setup properly at the publisher.
For the life of me I can't figure out how to do this on the desktop (Programatically setup a subscription and create a database using objects in the System.Replication Namespace and hopefully call Synchronize on it).
Question of such plan, to documentation to Microsoft SQL Server 2005 Express Edition it is written, that the given product supports only PUSH replication, however in his{its} structure there is an agent replmerg.exe which description says:
replication merge. Synchronization means, that the agent of merge transfers changes from the subscriber to the publisher, and then transfers changes from the publisher on the subscriber €¦
And so the question if this agent is present at this edition, whether that is possible to adjust manually his{its} call, or through Windows the agent, for realization PULL replication or it is impossible and for what reasons?
I have a huge problem after I noticed that SQL Server Agent has been removed
and need your advice about how to program replication between SQL Server Express and SQL Server 2005
So far I have encountered the following problems
SNAPSHOT
1. When I created my snapshot Irealized it wasnt running. I then read from the documentation that i had to resort to the Windows Synchronization Manager as there was no SQL Server AGENT within SQL Server Express to accomplish this function
I did this and it worked but wouldlike to control this programmatically as well
REPLICATION
2. Now I have a standalone SQL Server Express which is a subsciber to the SQL Server 2005 Database
How can I force the merge replication programmatically ie Vb.NET
yiotis writes "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.
Bottom line is, i need to be able to replicate via vb code 2 sql express databases with each other.
I want to replicate data from sql server 2000 to a sql server express 2005 box.
I can dts data across..but I want to be able to set up push replication from the Sql Server 2000 box, but the option to push the data to the Sql Express server in the GUI does not come up only Sql Server 2000 boxes are listed. Initially I will be using Snapshot replication but would like to eventually move on to transactional rep if only I can get it to push the data across.
Does anyone know how to acheive this thanks for any help Sammy.
I can choose synchronization direction for articles: a) Bidirectional b) one way
1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/
2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.
Wondering if anyone has any experience with SQL Server Express Edition (SSEXP). We're looking at a mobile sales force type model, so a local database on a laptop with no real time network connection. So the users would collect data locally, then connect up to the network every few days to replicate the data to a central server. So questions.. Has anyone tried anything similar? How stable/mature is SSEXP? Any other thoughts, alternatives or gotchas anyone can think of?
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea? Has anyone done this before o there are better solution?
