Hello, I am playing around with the replication feature within mssql.
The system that I am developing requires a master database at a remote location, possibly at a data centre and have a onsite database. The idea of having a local database is that the system can be running in the event of no internet connection and all the local databases update the master server and the master server updating all the local databases so they all have the same data.
I have some questions.
1. I create a publication on the master server and setup a subscription on a local database; does information only go 1 way? Or do I need to setup a publication on the local database and setup a subscription on the master server for data to go both ways?
2. What is the best type of replication for information on all databases to be up-to-date with each other, Merge?
3. Is there any limitations with the replication feature that I need to be aware of?
4. Is there anything that I need to keep in mind when I set this up?
5. How much bandwidth does replication take up, I know there are a lot of factors involved when trying to calculate this type of thing but a good idea would be good.
Thank you for all your replies.
*edit* I am testing with 2 copies of mssql enterprise trial
1) I haven't seen any use of "SAVE TRANSACTION" in small to mid size apps. Is it used mainly in big and complex apps? 2) Given this link http://www.databasejournal.com/features/mssql/article.php/3584751 about SQL2005. How can what the article presents be done in SQL2000? Thanks.
i have some questions about backing up mssql database, please, if you can help me with them.
1. what is main difference between Full Recovery and Bulk_Logged Recovery models? when to use one, and when to use the other?
2. why do i need marks in my backup? can you tell me in which case do i need marks? i don't understand, why wolud i ever wanted to recover database to some marked point? in which case i would like to recover only it's part and not full database?
3. if i do have backup of transaction log, it means nothing if i do not have backup of database structure? i need to have complete backup of transaction log to be able to recover all of the data?
4. i have database, and i did make no changes in database structure. in that case, only one backup of the system tables of the database is enough? there is no need to backup it all the time?
5. when i backup my transaction log file, do i backup only a differences between last backup of transaction log file and current, or i always backup complete transaction log file?
I am reading up on Replication because soon I must set up merge replication at two branch offices. The reading has confused me a little....
1. Do I have to run the Distribution database on another server than the one holding the headquarters database? I tried to configure replication on this server, and I get a message that says, "SQL Server Agent on ROCK currently uses a system account, which causes replication between servers to fail. In the following dialog, specify a domain account for the service startup account."
It then presents a dialog allowing me to add an account and password. I mistakenly assumed that it would create the account if it didn't exist, but it warned me that it didn't have sufficient rights to check the account, and was I sure that it existed with all the rights required? So I cancelled, to send this message :-)
When it says "Account", I assume it means create a virtual user? What rights should I give the account? I assume it will have to be able to do anything. What database should I give it as the default? Could I have the first agent not use a system account, and thereby run the distribution server on the same machine? (I have another machine available, I just want to know if I need it, or at what point of activity I would need it.) Will it matter whether the agent on this third machine usese a system account?
2. Virtually all the tables in the database use incrementing keys. Will I have to modify this at each branch, say seeding Branch 1 to start with values of 1, Branch 2 with values of 10000, and branch 3 with values of 100000 (or other sufficiently disparate values to prevent PK collisions?
3. How frequently can I merge the databases? Can it be done at different intervals with different tables? (I.e., there are a small number of tables in which updated values would be ideally propagated as soon as possible, while most of the tables are less critical. We sell tickets, so quantity-available is pretty important, whereas someone in one office hardly cares who purchased the tickets on a sale recorded in another office. This data we can wait for, but the counts are critical.)
Advice, etc. from those experienced in implementing replication would be most appreciated.
We have a subscription that is failing due to '24000[Microsoft][ODBC SQL Server Driver]Invalid cursor state'
My thought was to unsubscribe and subscribe again synching the tables. HOWEVER, I have been told that I cannot drop the table in question and have been asked to truncate instead. There are over 25 subscriptions to this article, so I am unable to modify the actual article. I started playing in the pubs database to see if I can do it before I messed with production...
I thought if I just change the .sch script located in the repldata direcotry to truncate table instead of create table, that would be it... However the drop table command seems to come before it even reaches that script, cause the error I get now is 'S0002[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot truncate table 'authors', because this table does not exist in database 'pubs'.'
My question is - when and where does the "drop table" command get executed and can I modify it without effecting the other subscriptions?
I checked the sysarticles and the column that says del_cmd is Null....
I'm having a headache with the following questions weavering around...Hope that someone can help me with the answers.
1) I've implemented a immediate updating transactional replication on 2 servers. The loopback detection is also set to true. Under normal circumstances, when there is an transaction at the subscriber, it should be replicated to the publisher. However, this was not the case here. There were some transaction in the subscriber, but they were not replicated to the publisher. Is it because of the "loopback detection" option?
2) If a bi-directional transaction is to be implemented as according to the BOL, there will be 2 distributors. When a publisher has a transaction, it will be replicated to the subscriber. When the subscriber commit the transaction, will the transaction be sent back to the publisher's distribution database? or will the distribution agent at the subscriber will know that it originated from the publisher and will not sent it to the publisher?
1) Do I have to install publishing on both servers (A and B) even though one will be publisher and the other will be subscriber.
2)a. Can named pipes be used for communication between these two servers which are on the same domain but not on the same network. Why or why not, whatever the answer may be? b. If I use TCP/IP, it the connection set up using the client configuration utility? How is the connection string set up in this case? c. Suppose the publishing server was not using Net-Beui. Could this pose any problems for communication. (is using lmhosts sufficient in this scenario)
3) I have set up a (remote) SQL Server to be a Publisher/ Distributor. Both SQL servers have been configured to be remote servers relative to each other. Following are the steps I have carried out to set up replication:
______On the Publication Server (a remote server) I went to Server --> Replication Configuration ---> Install Publishing
Next, I chose a local distribution server. I think that the instdis.sql script ran fine because the distribution database was installed successfully.
___Next, I went into Manage Publications from Server menu to set up the publications.
_____________When I went the subscription server to subscribe to the published articles, I got the following error message:
Error 14093: [SQL Server] You must be System Administrator (SA) or Database Owner (dbo) or Replication Subscriber (repl_subscriber) to execute the stored procedure.
1. When intitializing a new subscriber, data that is on the publisher is not being transferred to the new subscriber. Why?
2. Data that is already on the subscriber is not being uploaded to the publisher. Why?
3. When I perform a data validation, the validation fails, but there is no option to resolve the failure (ie, transfer data one way or the other). Why?
4. For the conflict resolver: I have a rowguid and a timestamp column on each article in the publication. It was my hope that by having the timestamp, I could avoid the need to manually reconcile the conflicts between publisher and subscriber. However, I see that the conflicts are still there and still require manual intervention to eliminate. Why?
5. Where is there additional documentation on the conflict resolver (such as what values to enter in the field "Enter information needed by the resolver")?
6. What is a "Local Subscriber"? As in the statement "Use the default merge resolver and create local Subscribers." as described in the "Choosing a Resolver" topic in SQL BOL.
7. What is a "Global Subscriber"? Same reference.
Sorry for all the ignorance. Replication is relatively new to me.
This customer has an SFA application. They are using NET CF 2.0 SP2, SQL Mobile and Merge Replication with SQL Server 2005. The device they are using are Symbol MC7094. It has integrated phone and is a Windows Mobile 5 Aku 3.
They have set up 5 differents publications for this applications. His business case makes them to have 1 publication to recreate all database structure and the first population of the tables.
For this they use AddSubscription .. Syncronize and the DropSubscription
They use the another 4 publications to sync particular tables because they don€™t want to sync everything, every time. All publications point to the same snapshot.
Each time they want to use one of this publication they instance the SqlceReplication object, then AddSuscription.. Synchronize .. then DropSubscription..
They have one of this publications that use a filter 1=0. Doing the previous steps SQLCE doesn€™t track the change and doesn€™t upload the data of the sql ce table to the server. It seems SQL Server recognize it as a new Sincronization, delete the records of the client and doesn€™t upload the changes (if I don€™t do the DropSubscription it works perfect) I can reproduce that using a device or an emulator.
They have 100 devices via GPRS - VPN. They need to be assure that this Add / DropSubscription will assure they don€™t lose the information. They want to assure all the process of sync is fine just to go to the carrier and make some GPRS connection monitor and test.
How is the best way to approach where the NET CF application needs to use more than one publication to the same database? I have suggested to put all the transactional tables in the same publication but due the business case it is not possible. What are the risks to use many publications?
Another question€¦ each X hours they do a full sync using the first subscription the application returns the next message: The snapshot for this publication has become obsolete. Why this happens? Due the changes in the another publications? How could we manage that to avoid this message? Note: All the test were doing via cradle and GPRS with the same results.
Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise. The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server. So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?
We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.
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.
I am seeing an unusual pop up when I try to hit the Website directory. I have setup replication setup for mobile units on IIS 6.0. When I try to hit the - http://defaultwebsite/test/sqlcesa30.dll - it tells me to open, save for cancel the sqlcesa30.dll file. This is weird. I have not seen this before.
When I hit the path from a internet browser - http://defaultwebsite/test/sqlcesa30.dll - it should come back with something like SQL Mobile Agent 3.0.
You will all have to excuse my ignorance. I'm a developer who also doubles up as a development DBA. I am however not particularly knowedgeable about all the really important DBA stuff.
We've built a small BI solution using SQL Server 2000. Our problem is that our server is getting on in years (5) and doesn't really have enough disk space or grunt. We havce a number of summary cubes that we've optimised quite successfully but our billing line level cubes run to 60 million rows and, well, they're about as quick as a dead ferret. Especially given the stupid queries our data analysts keep running.
We have however proved our point. That this can be done and indeed SQL Server can do it. So we're now looking at some infrastructure spend and some new copies of SQL2005.
But i need some advice. Our user base is climbing through the roof, we originally had 10, now we have closer to 50 and at this rate it'll be a couple of hundred by the end of the year. We're using a plugin called XLCubed to deliver that data into Excel from the Analysis Server.
The OLTP database that sits behind it is fairly robust but we have a number of web based apps (mostly lookup systems) that want to use the nice shiny new accurate tables of data we have created.
So I'm looking at a fairly big server to hold the OLTP DB, this will also serve up live data to our web apps. Its worth pointing out that the source data system is a batch system that processes overnight so we load data from yesterday at 6pm each evening and process our cubes and stuff overnight. Thus the data is a couple of days out of date. Don't laugh they used to use MS Access and got one mangy data set a month so this is a massive leap forward.
I wanted to mirror the DB to another machine but I also want to have a separate Cube Server. I wondered if the cube server could use the mirror to read its data from as opposed to loading the Main Server (the mirror would be an identical box) we would also have a separate box running some of our other systems acting as the witness.
I also wonderd about exporting the Cubes onto file shares for use locally as opposed to via the server which is how they connect now.
We have been using Reporting Services and some of the queries the devs write are not exactly efficient. So I was also planning on clustering a pair of smaller servers into a reporting farm. Could I use another SQL Server to serve data up to them? Could I use a DB snapshot to copy the data required to this server? What are the time / size implications of using a snapshot and replicating it over each night?
Any suggestions for places to read up on this? I've looked at the MS marketing blurb and while its big on buzzwords its light on specifics. Like how it actually works and how you would actually configure it to do some of this and what the implications would be.
Our existing SQL Server is experiencing some troubles and we would like to replace it with new hardware. Of course a migration will be in order to move off of this hardware and onto the new.
To better protect our business critical needs we have decided to implement SQL Server Replication. The SQL Server is what helps drive our everyday business. Without it, we are "dead in the water"! The other day, we lost approximately 10 hours of productivity until SQL Services could be restored.
My question is, if it would be wise to have yet a 3rd server used as a Distributor for Replication or if it would be okay to have the Distributor and Publisher on the same server?
I am concerned that there may be performance problems if we place the two roles on the same machine event though the hardware is very good. Below is the important server configuration.
Windows 2003 Standard Edition SQL Server Standard Edition Dual 2.4Ghz with HyperThreading 2GB of RAM RAID5 for MDF Files RAID1 for LDF Files
My goal is to make from time to time a (one way) replication from MSSQL server (Win2K) to MySQL (Linux, i mean no ODBC here).
Is it possible and how ?
The preferable way is to use the MSSQL's replication features, but I have another idea - a simple (php, perl - no matter) program on the linux machine which will be "cron"-ed to run at the requested time intervals.
What are your recommendings ? Thanks in advance for every kind of help.
I've got a nagging problem with the snapshot agent not being able to connect to the distributor.... I have a set up as follows.
I've set up the machine as a publisher, and as it's own distributor. Both the SQL Server and SQL Server Agent services are configured to run as the Administrator user (not ideal I know), and I've created a publication ready for replication. (the agent and distributor are on the same machine)
When I try and run the snapshot agent, Enterprise Manager just times out but a log in the event log says:
Event ID: 203 step 2 - The process could not connect to Distributor 'X'.
Thats about all the info I can find, nothing else is being logged.
Can anyone suggest anything I might have missed? - it's driving me up the wall!
Hi all.I need to set a oneway replication of some data from MSSQL (running underWindows 2000 server) to MYSQL (running under Linux).Do you have some ideas to solve the problem?Thank youLuca
Hi.. I tried to setup MSSQL2000 trans replicatiom using push subscription method. My Publisher is also my distributor. But whenever I started the dstribution agent, I got the following error. It has problem connecting to subscriber's DB with the following error. I had tried that the login is 100% fine. But why this error appear ? Any help?
The name ' ' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. (Source: Subsriber (Data source); Error number: 128)
I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.
I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.
Hi, guys.A very simple question for all of you: how can I get the amount ofbytes exchanged during a Merge replication between two Microsoft SQL2000 servers?Thank you.Bye,Angelo.-
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
i was planning to create a database migration tool .. its a certain database of a DMS (document management system) to another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
