MS recommends to use remote Distributor for mirroring. It looks like it will be a single point of failure. What are my options ? We can't mirror it, is failover clustering the only way to go (SAN being single point of failure) ? How would log shipping work, if I didn't get the very last completed transaction log fast enough to the other machine, so that recovered distribution database would be behind ?
Also, if distributor fails, would my transaction logs on publisher fill up the entire disk space ? Can it be stopped ?
I need to know if there are any other failover options available for SQL Server/NT, beyond Microsoft Clustered Services. If there are any, which ones are advised?
Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).
Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.
Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?
Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,
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.
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.
We have inherited a remote distribution db where the password has been lost. I would like to confirm that if you change the password in the distributor properties-->publishers--> administrative link password that the password will be changed on all publishers without having to resnap the publications?
I am planning to have AlwaysON Availability Group setup between Server 1 and Server 2
Server 1 -->Publisher-->2014 SQL Enterprise edition-->Windows Std 2012 --> Always on Primary Replica Server 2 -->Publisher(when DR happens)-->2014 SQL Enterprise edition-->Windows Std 2012 --> Secondary Primary Server 3 -->Subscriber-->2012 SQL Enterprise edition-->Windows Std 2012 --> NOT involved in Always ON Server 4 -->Subscriber-->2012 SQL Standard edition-->Windows Std 2012 --> NOT involved in Always ON Server 5(VM)-->Distributor-->2012 Standard edition-->Windows Std 2012-->not involved in Always ON
i know Publisher and subscribers can be mix match of 2014 and 2012 rom the above scenarios can i use (VM)SQL2012 std. as Distributor ? while publisher is being SQL 2014 ent.?
I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber
originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.
Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...
I am replicating a couple of SQL2K databases via transactional rep and remote 2K distributor to 1 SQL2K5 database.
I want the distributor to email me when/if any of the agents fail etc. In order to set this up, I think that I need the SQL Server service to use a domain account (from error messages that I have been getting). It is currently using a local system account (it was a test box in a previous life).
I have tried this briefly, and found that it lost the publisher details in the replication monitor. Can anyone tell me what the implications for changing the SQL server service user and password are? Am I going to have to tear down and rebuild replication? The SQL Server Agent is fine - I am trying to get the two services in sync.
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.
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)
SQL Server 2005 isn't accepting any connection other than SQL Management Studio from the local machine. However, even that's exhibiting strange behavior (please see below for more details).
I've read through all the common errors regarding enabling remote connections via the SAC tool, making sure protocols are enabled, etc. This case seems to be completely outside. Any advice appreciated.
Thanks,
Elias
[1] Client side: What is the connection string in you app or DSN? N/A. Can't connect via any remote method, including client Control Panel > ODBC > create DSN, SQL Server Enterprise Manager, third party application. If client fails to connect, what is the client error messages? Several, depending on method: "server does not exist or access denied", "error has occurred... under default settings, SQL Server does not accept remote connections", etc. Is the client remote or local to the SQL server machine? Remote Can you ping your server? YesCan you telnet to your SQL Server? No. However, there's a functional instance of SQL Server 2000 on the same domain which has no problems- telnet also fails to that instance, even locally from either machine using the 127.0.0.1 address. Telnet functions for other ports. What is your client database provider? N/A Or/And, what is your client application? SQL Management Studio and others Is your client computer in the same domain as the Server computer? Same domain What protocol the client enabled? TCPIP when I attempted to create the DSN. Not sure what protocol SQL Management Studio uses. Do you have aliases configured that match the server name portion of your connection string? NoDo you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005 What is the SKU of MS SQL? Standard What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ] Have tried all three individually and together. Configuration Manager exhibits strange behavior (see below)Does the server start successfully? Server starts successfully when the machine boots. However once it's stopped, it can't be restarted (see below) If SQL Server is a named instance, is the SQL browser enabled? Default instance What is the account that the SQL Server is running under? Local SystemDo you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? Not sure, but SQL Server 200 instance is working OK Do you make firewall exception for SQL Browser UDP port 1434? Unknown
[3] Platform: What is the OS version? Client: Windows XPSP2 Server: Windows Server 2003 Do you have third party antivirus, anti-spareware software installed? Client: Trend Micro Server: UnknownOther behavior:
A separate instance of SQL Server 2000 on a different machine on the same domain is functioning without problems.
Telnet to either SQL Server (functional 2000 or problem 2005) on port 1433 is not possible - even when attempted from the local machine using 127.0.0.1! Telnet to both machines works using other ports.
When creating a DSN from a client machine, problem 2005 instance is in list of available SQL Servers, however connection fails ("SQL Server denied access or does not exist").
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
MSSQLSERVER service starts automatically on boot. However, once stopped the service can not be restarted. Message given: "SQL Server (MSSQLSERVER) service on Local Computer Started and then Stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."
SQL Server Surface Area Configuration for Services and Connections tool does not display Database Engine option as indicated by this MS guide to enabling remote connections: http://support.microsoft.com/default.aspx?scid=kb;en-us;914277
SQL Server communication manager shows TCP, Shared memory, named pipes enabled. However event log seems to show instance not listening via any protocol. Message displayed as: "SQL Server listening on ." (should read: "Server is listening on ['any' <ipaddress> <ipv4> <Port Number>])
Several answers indicated uninstall and reinstall could solve these problems. However, wizard refused to uninstall SQL Server.
Attempted disabling Shared Memory as documentation indicates this protocol gets used first. No luck.
Access through SQL Server Management Studio works, from local machine only.
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.
I am trying to install SQL Server 2012 onto an already configured and validated windows failover cluster (server 2012) but the process is hanging after installing the setup files.
The last entry in the log is:
running discovery on remote machine
and I've left it hanging like this for 4 hours and nothing happens.
1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk. 2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode. 3. after fail over done to 2nd secondary node what mode in production(readonly or read write). 4. how to rollback to production primary ,will change data in secondary will get updated in primary.
We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.
However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.
The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.
Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.
The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.
Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.
Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.
We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?
1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. After fail over done to 2nd secondary node what mode in production(readonly or read write).
4. How to rollback to production primary ,will change data in secondary will get updated in primary.
After extensive testing I have found that bcp's run almost twice as fast on my dedicated Distributor than on either the Publisher or Subscriber. I was wondering if there is a way to make the bcp's spawned by creating a Snapshot run on the distributor and not the Publisher.
I was wondering if you could set up multiple distributors for one database?For example:Server A needs to publish data to server B so I set up server A's distributor as server B. Later Server A needs to publish data to server C so I want to set up the distributor for this replication to use server C.I don't think this is possible as you need to set up the distributor for the entire DB when its created not for each individual replication job.Any help is appreciated.Nick
I am having problem creating a distributor on my sever, when i do it thru the enterprise manager it defaults to another sever's path and when i put the severs path it dosen't find it. and when i do it thru query analzer this is what i get
Server: Msg 6, Level 16, State 1, Procedure sp_adddistributor, Line 147 Specified SQL server not found. Remote logins for remote server 'repl_distributor' have been dropped. Server dropped. Server added. Server network name set.
and for this it is going to another server sp_adddistributor @distributor = '158.72.80.48' ,@heartbeat_interval = 10 , @password = 'grants831' -- , @from_scripting = from_scripting
error message
Server: Msg 18482, Level 14, State 1, Procedure sp_adddistributor, Line 147 Could not connect to server 'GEMS' because 'vivian' is not defined as a remote server. Remote logins for remote server 'repl_distributor' have been dropped. Server dropped. Server added. Server network name set.
Completed SQL 2000 Transactional Replication from distributor/publisher to Subscriber. (with identity problem solved)
NOW wizard disallows set up subscriber to publish back to the Distributor/Publisher. "Distributor not known" message on Subscriber server when we open configure Publisher-Subscriber tool.
Any experience using this wizard to set this up would be appreciated.
For remote distributor (transactional replication,distribution database 50GB, 3 day retention), what is the most critical resource except disk ? Is it CPU intensive ? does it use lots of memory (i.e. would 4GB be enough) ?
Hello, I have 3 servers sql 7 sp3, 1 editor, a distributor and an subscriber. the base on the editor is removed, how to make to remove all traces of replication on the distributor and the subscriber. thank you. Pascal
I'm having a problem configuring one of my SQL servers as a distributor. I've done it successfully on another server, so I know that it's nothing procedural and there must be an issue with the way the server is set up. When I click 'finish' on the Configure Publication and Distribution Wizard, I get the following error message:
Enterprise Manager could not configure 'ABEL' as the distributor for 'ABEL'.
Error 20017: Cannot add the remote distributor at this time. Make sure that the local server is configured as a Distribution Publisher at the Distributor.
Can anyone point me in the right direction? I'm fairly new to SQL Server, so feel free to explain things in a condescending manner. ;-)
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?
Hi this is SQL 2000 but I couldn't see a forum for SQL 2000 replication.
I have set up merge replication. Between a server and an msde laptop.
The sync works fine within the network but when the user goes home and vpn's into the network and tries to replicate they get an error message that says:
"...server is not configured as a distributor" I find this strange because the replication definitely works within the network.
I have publisher and distributor, both on SQL 2005. I need to re configure replication. When I try to create publisher, the wizard gives me an option to give password to connect to Distributor. Even after resetting this password on the distributor server, publisher still gives me the following error.
SQL Server could not connect to the Distributor with the specified password. SQL Server Error 21670.
Hello,I'm trying to set up transactional replication on two SQL2k Servers.Unfortunately I stuck on the following problem:I use 'Configure Publishing and Distribution Wizard' , I make one of theservers (publisher) its own distributor, then I set all the paths .Afterpressing 'Finish' button, the wizard tries to perform next step i.e.'Configuring Distributor' and I get the error:"Error 14114: '(null)' is not configured as a Distributor". Does anyone haveidea what I should do in order to solve the problem?I tried to run procedures manually, and I get exactly the same error when Itry to execute sp_adddistributondbThanks,Piotrek
I am trying to "Configure Distributor" prior to setting up the publications. When I go through the wizard, at the very last step I get the following error:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot find the principal 'sa', because it does not exist or you do not have permission. 'distribution' is not configured as a distribution database. Changed database context to 'master'. (Microsoft SQL Server, Error: 15151)
