Hello All,
The setting is SQL server 7, on Windows NT.
If the distributor can not access the log on the publisher database ( but
subscribers are not deleted yet), will the full backup of the publisher
database truncate the transaction log? My guess is, it will not truncate the
log.
Can any one confirm please?
Thanks,
Mokles
I backed up my transaction log file using this:
BACKUP LOG iTest2_AELetters TO DISK = N'nul'(yes I know this breaks the log chain - it's expected in this intance)
But after I run it my t log is exact same size. I have refreshed many times.
I am using SQL Always On on this DB.
Does anyone know if there are any known issues with using a 64 bit distributor and 32 bit publisher together?
This is SQL 2005 and Push Merge replication.
Thanks in advance
Martin
hi,
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers.
Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
I AM TRYING TO SET UP A DISTRIBUTOR ON SQL SERVER 2005.
THE LAST STEP WHEN I AM TRING TO CONNECT I GET THE ERROR BELOW:
“ORACLE SERVER INSTANCE 'FOOBAR' CANNOT BE ENABLED AS A PUBLISHER BECAUSE OF THE FOLLOWING ERROR:
ADDITIONAL INFORMATION:
UNABLE TO RUN SQL*PLUS. MAKE CERTAIN THAT A CURRENT VERSION OF THE ORACLE CLIENT CODE IS INSTALLED AT THE DISTRIBUTOR. FOR ADDITIONAL INFORMATION, SEE SQL SERVER ERROR 21617 IN TROUBLESHOOTING ORACLE PUBLISHERS IN SQL SERVER BOOKS ONLINE. (MICROSOFT SQL SERVER, ERROR: 21617)�
RESEARCHING THE ERROR IT STATES TO MAKE SURE SQLPLUS IS SET UP CORRECTLY IN THE PATH. THE PATH IS CORRECT. TESTING SQL PLUS IT WORKS; AND I CAN ALSO CONNECT TO THE ORACLE DB IN BUSINESS INTELLIGENCE.
ANY HELP WOULD BE APPRECIATED.
Hi - hoping some folks here can help me. I've got a SQL 2000, Service Pack 4 database that I need to publish, but I'm getting the following error on the exec sp_addpublication statement when I try to set up publication in Management Studio:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
I also tried to set up the Publisher in Enterprise Studio, on the theory that the system sproc was not backwards compatible, and there I got this error:
Error 627: Cannot use SAVE TRANSACTION within a distributed transaction
There is one proc in the publisher's database that uses SAVE TRANSACTION, but it isn't currently being run and it isn't dependent on the table being published. Has anybody else encountered this? Any ideas about how to make this go away?
I am setting up  transaction replication in SQL Server 2012 with three individual servers for Distributor, Publisher, Subscriber, But I want to know how to set the folder for Snapshot agent like whether the folder should be a network shared folder or normal local folder.
View 4 Replies View RelatedI am investigating scenarios for our new replication strategy and we have 2 very powerfull machines. 1 however more powerful than the other.
The distributor will be pushing (merge) data to 40 replicas around the globe, the database is about 5GB in size and uses dynamic filtering to sort out who needs what data.
The publisher will only be working with the distributor it is not doing any other work.
I am guessing the distributor will have the majorty of the work to do in this case and should have the more powerful machine. Does anyone know if this is correct?
Thanks in Advance
Martin
I have Oracle 64-bit drivers installed. I can run SQLPLUS and it works. I can create linked servers to oracle and that works also. I am having this error when I try to add oracle as a publisher. OraOLEDB.Oracle is already set to 'Allow InProces'.
Msg 21626, Level 16, State 1, Procedure sp_MSrepl_testconnection, Line 110
Unable to connect to Oracle database server 'DTMART' using the Oracle OLEDB provider OraOLEDB.Oracle. For addition information, see SQL Server Error 21626 in Troubleshooting Oracle Publishers in SQL Server Books Online.
Msg 21747, Level 16, State 1, Procedure sp_MSrepl_adddistpublisher, Line 463
Cannot establish a connection to the Oracle Publisher 'DTMART'. Verify connection information and ensure that you can connect to the Publisher through a tool like SQL*PLUS.
Thanks..
Hi All,
Can push and pull subscriptions be configured on the publisher/distributer server?
We have server A running SQL Server 2000 configured as Publisher/Distributor and also a Push subscription (transactional replication) to Server B configured as Subscriber.
The idea was to have Server B as backup server if Server A goes down. Users will continue to update data in server B till server A is up.
Once server A is up, I want to synchronise the up-to-minuit date from Server B to Server A in the form of Transactional replication configured as "Pull Subscription" from Server A.
Is that possible?
Thanks
Sri
Hi all,
I am having a problem now. I cannot drop my publisher and publication because i was uninstalled the remote distibutor before i disable al lthe publication. Now the remote distibutor no longer exist.
Can another one help me? Urgent!!
hi,
Im I want to configure my sqlserver 2005 server as both subscriber and distributor for transactional replication purposes.
I want to configure the sql server 2000 as the publisher.
When I try to configure the publisher in the sql 2000 server, Im not being able to establish the connection with the distributor 2005 sql server in any of the 2 possible cases.
case1-Using windows authentication- message:need to use management studio or SMO
case2-Using sql server authentication-message:authentication failed
Question:
Is it possible at all to do this? or should I make the distributor to reside in a sql 2000 server?
thank you
For transactional replication, are there any issues and is it even possible to have 32-bit SQL 2000 publisher and SQL 2005 64-bit distributor and subscriber? Thanks
View 1 Replies View RelatedWe have a SQL 2000 Server (publisher & distributor) with around 15 databases being replicated (all using merge replication).
My question is the following:
Is it possible to perform Merge replication from a SQL 2000 (acting as the publisher & distributor) to a SQL 2005 server acting as the Subscriber)?
If anyone has a link to any instructions somewhere that would be great.
Thanks
Hi,
We are about to alter our replication architecture from
SQL 2K publisher/SQL 2K5 distributor/subscriber
to
SQL 2K publisher, seperate SQL 2K distributor and SQL 2K5 subscriber.
The subscriptions will be pulled from the distributor and pushed to the subscriber. Basically, all the work will be done by the distributor (in the hope of improving performance).
Has anyone had any experience of a similar architecture? All SQL servers are running with the latest service packs.
Hello,
Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?
Thanks in advance,
John
Hi all,
I have set up a maintenance plan to backup my databases but when I view the maintenance plan history the transaction log backup steps have a success tick but a message saying :
"Backup can not be performed on this database. This sub task is ignored"
I have looked in my backup directory and only see *.BAK files and no *.TRN.
The transaction logs are supposed to back up at 1AM and the databases at 2AM
All my databases being backed up have the truncate log on checkpoint option set. Is this best practise according to my backup schedule ?
Help !!!!
Regards,
Tim
I am not able to connect to Remote Distributor from Publisher using Administrative Link Password. I have configured the Distributor on 1 m/c and Publisher on another m/c. When i use the wizard to configure the publisher using remote distributor. I have also specified the same Admin Password link (distributor_admin) in the distributor m/c.
Â
I am getting the following message:
Â
TITLE: New Publication Wizard------------------------------
SQL Server could not connect to the Distributor using the specified password.
[URL]
------------------------------ADDITIONAL INFORMATION:
Connection to server [SANMENON] failed.OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "Login timeout expired".OLE DB provider "SQLNCLI" for linked server "D956CF83-AE2E-4FC5-83DD-BE90D84A3950" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 21670)
[URL]
------------------------------BUTTONS:
OK------------------------------
Coming from a (mostly) Oracle shop, I am unclear how SS transaction logswork as far as up to point of failure recovery goes.I have the few MSSQL databases I look after in full recovery mode, and Iset up maintenance plans to back these up online on a daily basis. Inthe research I did it was recommended backing up the transaction logs aswell on a frequent basis to ensure recovery to POF or point in time.I had assumed that they work like Oracles archive logs, in that oncethey are full they are archived to disk in case they are needed, toduplex destinations if need be. Does SS do this, or do I need to bebacking up transaction logs on an hourly (or shorter) basis? In lookingat enterprise manager I see that it does not look like the transactionlogs are archived anywhere, but overwritten once filled up, similar toOracle noarchivelog mode, in that if you lose your online redo logs oranyone of them has recycled since your last backup, then you can onlyrecover to the last good backup.Is this a correct assumption? Should I be backing up transaction logshourly to make sure I can have PIT or POF recovery?thanksTG
View 3 Replies View Related
Hello,
how can i view the content of a archived (backed up) transaction log?
DBCC LOG only works with online logs.
Thx for your answer
Best regards,
Stefan Ridinger
We use AlwaysOn availability groups, which has 2 SQL nodes configured (version: 11.0.3373.0), my full and differential backups are working for all my databases, however I am unable to perform any LOG backups.
I have double checked my Availability Group settings, and the backup preferences is set to: 'Prefer Secondary'
I've tried creating a maintenance job as well as using Ola Hallengren's maintenancescript job to back them up, but nothing is written to the drive. All jobs return successful every time, and take less then 3 seconds to run. There are no events being written in the SQL error log or event log.
Hi ,I have a file presumably backup of a User database from a SQLServer(2k) database. I want to load/restore that in a local SQL Serverto be able to see the table , views , etc present in the database.How do I do that ?Thanks,Arijit
View 4 Replies View RelatedI have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
How can I check if the database is currently being backed up?
Seems like that should be a property, but it's not an attribute of the DATABASEPROPERTY function.
Thanks
Pls tell me,can we store or determine the date/time of a database backed up or can we store date/time while taking back up of a database
View 2 Replies View RelatedMy SQL Server 2000 database log was full (2MB) and I am unable to add new column to a table. So that I follow this article to truncate my log file to 1MB. It works. But when I add a new column to a table after that. The log file went back to 2MB in size and I got the "the log file is full" error message again for the operation. BTW, my reovery mode was set to "Simple". I don't understand how the save table operation can recover my change on log size. What should I do to "permanently" truncate my log?
http://www.support.microsoft.com/?id=272318
I did this:
DBCC SHRINKFILE (myDB_Log, 1)
BACKUP LOG myDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE (myDB_Log, 1)
The transaction log in a database in our SQLSERVER-2000 server hasgrown to 16GB. I cannot shrink the transaction log manually because itsays that the entire 16GB log size is not free. This is strangebecause we backup the transaction log every hour, and that should havetruncated the transaction log, and should have limited the size of thetransaction log; somehow, the entire transaction log is still markedas being used.I was under the impressionI believe that must have something to do with the fact that thedatabase is a part of our nightly replication. The reason is that whenI tried the following commands, I got that error message:checkpointdump transaction isprod with no_logThe log was not truncated because records at the beginningof the log are pending replication. Ensure the Log ReaderAgent is running or use sp_repldone to mark transactionsas distributed.What does this mean? How can I get away from this mess?Thanks.Jay Chan
View 5 Replies View RelatedI was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.
Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?
Precedents
Windows 2000 Server
1 SERVERNAME
SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
1 SERVERNAMEINSTANCE
SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 4)
IIS 5.0
SQL Server CE 2.0 (sp3a)
Merge replication previously working fine.
The problem
After migrating MSDE from sp3a to sp4 (in order to fix CE replication bug) I can't sync to MSDE.
I got the following error code: [29045]->[Initializing SQL Server Reconciler has failed.] so I've looked into SQL Server CE log file and I've found the following error message:
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000
The process could not connect to Distributor 'Distributor-name'. 20084
2006/02/07 19:44:23 Thread=9A8 RSCB=4 Command=SYNC Hr=00000000
Data source name not found and no default driver specified
I've already tried without success
Uninstalling all SQL Server 2000 (EE) instances (except for the "no instance")
Unistalling and re-install MSDE (sp4)
Re-registering sscerp20.dll.
Rebooting server.
Restarting IIS.
Any ideas?
Sebastian
Hi
I often deal with a problem when replicated database is down due to
electric outage or any other scheduled type of downtime. Primary
database is up but distribution agent is going down 'after 10 attempts
to connect to replicated database'. That's the problem I think because
not only distribution agent goes in undetermined state and I have to
restart sqlagent service, but I need to go through whole process of
snapshot and applaying it to replicated db. I would like it to
accumulate transactions in distribution database and once replicated db
is up - distribute them.
I wonder if anybody can tell me how to configure distribution thread so
it does not go down after 10 unsuccessfull attempts but stays for day
or two.
Thank you, Gene.
Hi,
Before this, I've 2 difference site that run SQL server 2000 as database server. One at north and the other at west.
At north, I've a group of terminal that run SQL server 2000. I've 1 main server that run as publisher and distributor to all the terminal. And also the main database is at the main server. The terminal will replicate together using merge replication. So that the data would be same at all terminal. At west it also have the same scenario.
Last week my boss ask me to replicate both main server ( north and west ). So that he want the data would be same at all place.
So, can anybody help me.....
Recently we've upgraded SQL 7.0 to SQL 2000. We use a Database Maintenance Plans to backup the databases and transaction logs. Everything seems to be working, but I’m getting an error message in the event viewer that has been described in Microsoft Knowledge Base article 818202 "PRB: A'Database log truncated" Error is logged in the Event Log When you try to Backup the Transaction Log. The article describes the cause of this warning, but does not give any fix or some kind of work around. Does anybody knows what to do or may be had this problem before? Please help. Thanks a lot.
View 6 Replies View RelatedHello:
I am running an application on mssql 6.5, sp4.
We are working on using a database as a publisher and a remote database as a subscriber for one replication in Canada.
WE havw just been told that another application on a different server(publisher/ distribution) wants to replication to our database as a subscriber.
We also will need(as a publisher/distribution) to replication to a second subscriber database after we completely test the first replication to Canada.
Therefore my questions are:
1) Can a database on a local database -- our database -- on one server be a publisher and subscriber to ther different databases?
2)has any one been involved with replication on a database used both as a publisher and subscriber to different replications requirements?
What are the pitfalls aside from the scheduling conflicts?
3) In terms of database space requirements, how much disk space extra do we need? What guidelines should we use? For each table as an article, assuming we are doing the full table, do we add 100% more space or what?
4) Any other pitfalls we should be aware of?
Thanks.
David Spaisman