SQL Server Replication From Non-clustered Database Servers
Jun 22, 2006
Hello,
Being a bit of a SQL Server novice, need some advice with the following
situation.
Server A and Server B have SQLServer 2000 based databases. The vendor
of the application/system has implemented their own replication process
to ensure the 2 databases are in sync. However, there is no clustering
with virtual IP addresses implemented. So to an external client/db, it
is 2 identical databases with the same name on 2 distinct servers.
We need to develop an application that will reside on a networked
server C and with SQLServer 2000 as well. While most of the tables in
this database are self contained, around 10 tables will have to be
mirror copies of the same tables from either Server A or Server B.
Question, how do we implement subscription based replication on top of
a redundant database, when no clustering is implemented? So, in
essence, when Server A is alive, the database on Server C will
periodically (or on change) replicate the 10 tables from Server A. When
Server A is not alive, it needs to do same from server B. (When both
server A and B are alive, it is acceptable to get data from either,
since they are synchronized internally).
Any alternate suggestions on achieving this functionality are welcome
too. If SQL Server 2005 has some capabilities that may address this
problem, that is a consideration as well.
Just want to know if we can replicate data (Transactional Replication) from a clustered servers (server A and Server B are active-passive clustered running SqlServer 7) to another server C running SqlServer 7.
If yes, how to go about doing this. Any white papers, KB articles or books out there which will walk through the steps to do it.
We've had this issue for awhile, but I have not been able to find a resolution for this. We have 2 identical servers (hardware & software) set-up: IBM x3850-(88633RU) running Windows Server 2003 Enterprise x64 (5.2.3790 SP1 Build 3790 R2). Server A was running SP1 (Build 9.0.2047) on a 3-node active-passive-passive failover cluster. Server B was running SP2 + Hotfix KB933097 (Build 9.0.3152) on a 2-node active-passive failover cluster. Both these servers are heavily utilized, and when originally installed with these versions of SQL Server Enterprise 2005 experienced no issues during or post-install. The original install of SQL 2005 Enterprise on both these servers had the Cluster Group Selection "Data files" setting changed to "M:MICROSOFT SQL SERVER" so that all system dbs and binaries would be installed to that location. On Server B where SP2 had already been applied, the update was downloaded on May 1, 2007, well after the GDR issues which had been plaguing SP2 releases was supposed to have been resolved.
Our problems began the night when we tried to apply hotfix KB933097 on Server A, and cumulative update packages 1,2,3 on Servers A & B, as well as catch-up the same SP2 and hotfix packages on other non-clustered servers where default system db locations remained the same. On all servers other than Servers A and B, the installs proceeded smoothly and without issue.
On Server B, which was already running SP2 + Hotfix KB933097, we tried installing Cumulative Update Package 1 (Build 9.0.3161) on the primary node. The install proceeded until the following errors were encountered:
"[Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_resource_semaphores', because it does not exist or you do not have permission."
"[Microsoft][SQL Native Client][SQL Server]Cannot find the object 'dm_exec_query_memory_grants', because it does not exist or you do not have permission."
After this, the setup finished with the message "Product: Microsoft SQL Server 2005 (64-bit) - Update 'Hotfix 3161 for SQL Server Database Services 2005 (64-bit) ENU (KB935356)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB935356_sqlrun_sql.msp.log."
We rebooted the server and tried several times to reinstall the hotfix as sa and as the database service account with full windows and sa credentials, each time with no success, and rebooting between retries. All other components were installed successfully. We were hesitant to try uninstalling the hotfix, since it was unable to be removed successfully in previous attempts. However, when I connected to Database Engine using Management Studio, I found that the version of SQL being reported was in fact Build 9.0.3161! Everything also appeared to be running properly.
I tried to to Google the answer, but could only find information as applied to SP1 when system dbs master and mssqlsystemresource were located on different locations (http://support.microsoft.com/default.aspx?scid=kb;EN-US;918695 ). I had already verified that all system dbs, including the ones mentioned, were originally and currently located together on M:Microsoft SQL ServerMSSQL.1MSSQLData.
Finally I contacted a fellow DBA who advised me to continue installing all the other hotfixes, and then compare all SQL files against other servers where the install succeeded to ensure all system files were updated correctly (matching file sizes and datetime stamps). Since we were depending on a fix in Cumulative Update Package 3 to fix a critical log-shipping security issue, I went ahead with the rest of the installs, getting the same error with each package, rebooting between each install.
We found after the final install that the mssqlsystemresource files in the M:Microsoft SQL ServerMSSQL.1MSSQLData
directory were in fact updated after each install with a new timestamp, and that all files in C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn matched on both file size and timestamp. The log-shipping security issue we were looking to fix also appeared to be resolved by the last update.
On Server A, which was running SP1 (Build 9.0.2047), we installed the same SP2 package as we had on Server B with no issue. We rebooted and then tried to apply the same Hotfix KB933097 (Build 9.0.3152) that had been applied successfully on Server B and found to our surprise that the same errors were being generated here as well. We continued to apply the rest of the hotfixes so that both servers matched software updates. Comparing the above directories showed that files seemed to have been updated, even though database services appeared to be not installed.
It looks like we now need Cumulative Update 5 hotfix package in order to deal with WMI alerting issues, but I'm afraid to apply it until we have a known resolution for the past errors.
Since this issue was supposed to be resolved by SP2, I'm not sure why these post-SP2 hotfixes should fail. It seems to me to be an issue of the directory location of the system files which is in question.
I can post the hotfix log files as well, if anyone is interested in seeing them.
Any help would be greatly appreciated. As well, does anyone have any idea when SP3 is scheduled to come out? I really don't like applying hotfixes which are usually untested for general purposes, and it seems Microsoft is delaying this in favour of releasing SQL 2008.
We are running 2 SQL Server and both run in failover clustered Environment. The Problem is now we need to Replicate a Database from one Virtual SQL Sever to the other.
The Second one (clusterd environment)is stroing their database localy while the First One (clustered environment)is storing database in a shared storage. Note that Both Server are used for sperate purpose , but we now need to set replication on the Other Clustered Setup for Reporting Purpose.
Will it work if we configure replication from One SQL Server Clustered Setup to the Other Clstered Setup. If yes, then please let me know how it can be done ?
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
I am trying to install failover support for my sql server 7.0 database on the in clustere server enviorment Now We have operating system on two clusterd servers but just one common database disk Now what will happen if i lose server A ( Active ) the server B will take over and use the same database . But what will
happen if database disk crashes IS it possible for me to replicate the data or use disk mirroring , or have one clustered
database server seperate How does the database server cluster work ? What kind of replication do i use for my backup support ? what should be my recovery process ? how can i use standby option on the database during recovery ?
Please try to reply as soon as possible if you can also point to some documetation on the net that will also help
Also look at my current structure ( but this should not affect your answer )
Tables
group 1 --- 20 tables ( being replicated to server c ) group 2 ----60 tables
on cluster 1 Server A-- Holds Group 1 ( Transictional replication to server c )
on cluster 2 server B---(Production server ) Group 2 + Group 1
According to Microsoft, if you need to change the subnet for the clustered servers, you will need to either edit the registry or reinstall virtual SQL Server.
Does any one know which registry key(s) store the subnet information? I was reviewing the clustered key but was able to find the key for the IP address only.
I've been asked to write a script to monitor whether a clustered server is up and alive and if so which node it's actually running on. Apparently there's been some problems of failover to the passive server without anyone knowing that it happened and they want to know. Any suggestions?
I've run into a problem attempting to change my service account on the clustered servers from an administrative account to a non-privileged account under SQL Server 2005 Enterprise Edition. When I change the login properties in Configuration Manager I get the following error:
"The user already belongs to this group"
I'm then prevented from making any changes to the service account. I don't know what I'm supposed to do at this point to resolve the problem, so any assistance will be greatly appreciated.
I have 4 servers, 2 each for application (Dev & Prod)
DEV 1 & DEV 2 are standalone servers
Prod 1 & Prod 2 are Windows Clustered Servers.
From one application to other we do Distributed transactions. Dev 1 - Dev 2 or Dev 2 - Dev 1 can start DTC and working fine,but issue comes when Prod 1 - Prod 2 or Prod 2 - Prod 1. I get error message OLE DB provider "SQLNCLI" for linked server "xyz" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 3
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "xyz" was unable to begin a distributed transaction.
I have tested Dev 1 - Prod 1, Dev 1 - Prod 2, Dev 2 - Prod 1, Dev 2 - Prod 2 everything is working fine only Production servers are causing issue.
I enabled all settings needed for DTC on Cluster MSDTC service but no luck.
We have two SQL servers, one in NY, one in Illinois, and we want to sync them. Suppose we have a T1 line as backbone, which one is a better solution for us, Clustered or SQL replication ? Your help will be greatly appreciated! Xiao
I have clustered SQL server machines(SSserver1 and SSserver2). I need to restore a database from another standalone server to this clustered environment. In this case do I need to restore the DB on both the nodes that are part of the clustering(SSserver1 and SSserver2) or Just can I do it on one of them?
Using SQL 2005 SP2. I have a publication that contains indexed views, and some other objects that query the indexed view using WITH (NOEXPAND). Currently replication fails because the CLUSTERED INDEX on the view is NOT replicated. I've experimented with various schema options but nothing changes. The view is replicated but not the clustered index on that view. I've seen some discussion on replicating indexed views to a table, but I would like to replicate indexed view schema fully. (Including the clustered index on that view). Is there a way to make this work?
I set up replication on our servers at work to streamline some procedures we run daily/weekly on them. This copies around 15 articles from two databases on the "Master" server to another server used for execution purposes. For the most part it was a pretty straight forward task and it seemed to work nicely; but I realised after some investigation that the non-clustered indexes weren't copying over to the child server.
I set the non-clustered indexes property in the properties of the publishing articles to "True" and generated a new snapshot, this seemed to work, but I've come into work this morning to find the property has reset to "False" and I have no indexes on the table again. Why is this happening and is there any way I can resolve the matter so the indexes are copied over concurrently?
I am getting the following error during replication of Database to a client:
The schema script 'Statutes_6.dri' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001
Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed. (Source: MSSQLServer, Error number: 7696) Get help: http://help/7696
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Source: MSSQLServer, Error number: 319)
The database is relatively small, only about 5 tables but there is a clustered Full-text Index.
My ambition is to run ASPStateInMemory with durability on, supplementing the in-memory database with an on-disk version to give session state persistence when and if the clustered instance of SQL runs into, say a network card failure and needs to fail to a partner node. I understand that DAG's are usually run between standalone instances of SQL running on different machines. But, can I combine the DAG with the FCI? Instead of using a remote standalone SQL server, can I just another instance in the same cluster?
Not sure if this question belongs here in the Setup & Upgrade section but here is the problem.
When installing replication in a non clustered environment, the Sql replication jobs run fine with the windows login provided (this login has access to the snapshot folder and has the proper roles. All the log reader, distributer and subscriber agents work fine)
When installing replication in a windows (MS) clustered environment, the agents running under the same login and same privilages dont work. It seems like there is something wrong with the proxy account and the ability of sql to access disk resources using this account. The only workaround is to go to the agent jobs, change the login under which they work from the proxy account to an actual windows account.
Has anyone come across this issue? I am forced to use the sql agent account and running the replication agents under way more privilages than I would like to.
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
Can someone recommend a utility (preferably open-source) tosynchronize changes across servers? I need to bring only data over insome cases, and only objects in other cases. Any ideas?Thx!
We recently installed another SQL Server so we could test performance of replication between the two servers. After I set up the replication between the two I got the following error: subscriber must be running in Per Seat (Named User) licensing mode to use this replication feature. Is there any way around this? Or could you direct me to some documention pertaining to this error? Thank you
We have a project that will consist of 3 production servers in 3 different regions and each server has it`s own clients in that region. We want to use replication to keep them all in synch so that if one goes down, the client PC`s would just re-connect to one of the other two remaining servers and keep working.
1. I am wondering what type of replication to use? 2. I was leaning towards merge, but was wondering how to use it to keep 3 servers in synch? If I use the Central publisher with updating subscribers scenario, I foresee a potential problem as follows:
A. Server1 is central publisher B. Server2 and Server3 are updating subscribers C. Server2 (or Server3) goes down, clients reconnect to Server1 and keep working. D. When Server2 (or Server3) is back up, Server1 will push all changes back to Server2.
but, E. Server1 goes down, clients reconnect to either Server2 or Server3 and keep working. F. Since Server1 is down, changes made to Server2 not replicated back to Server3 (The central publisher is gone) until Server1 comes back up. G. Server2 goes down while Server1 is down, clients re-connect to Server3. H. The changes made to Server2 were not replicated to Server3 because the central publisher is missing. I. Server3 therefore is missing data changes.
Please help me figure this out. I feel as if I am missing something that I could do to also keep Server2 and Server3 in synch with each other besides the Central Publisher.
suppose i am repliacting(Transactional Replication) to two servers using the same publications can i stop replicating to one server without disturbing the other server? If i resume the replication to the server after two days what will happen to the two day's data?will they be replicated when i resume the replication? Thank you
Server_1 contains DB_A and DB_B. Server_2 contains DB_A and DB_B. Can replication be setup where Server_1 DB_A (publisher) publishes to Server_2 DB_A (subscriber) and Server_2 DB_B (publisher) publishes to Server_1 DB_B (subscriber)? The environment is NT 4 (sp3) and SQL 6.5 (sp5a). Thanks in advance.
I have two SQL Servers (6.5). One based in London (LDNINNTS9) and one in New York (NYINNTS2).
I had bidirectional replication set up on one table in a DB and everything is (WAS!!!) working fine. Came to work this morning (Monday typical!) and the following has happened.
When I select the remote servers option on the NY server from Enterprise manager I get the following error message
Unable to connect to site '0'because '' is not defined as a remote server.
Now it says that '' is not defined as a remote server. So I did a select of @@Servername and it has returned (null). It was set to NYINNTS2. I have tried executing some ISQL to change the name and started and stopped the SQL Services but the name remains (null). Does anyone have an idea of what I can do to set the name up as it was?
I know that the Server name gets set to null if it is invalid, but I am trying to set it to NYINNTS2, which should not be invalid? Does the domain name impact the validity of the name?
Anyone know what or how to calculate how much bandwidth I may use during replication. I have done this with large pipes with out a problem, just concerned I may need to change the interval to something else.
How it is possible to perform "replication" between two databases using technological means of MS SQL Server 2005 if two servers do not have a connection at all. The only thing is possible - is transfer of data for the replication on a CD.
What is the best way to do it if central database has a hundreds of such "subscribers"?
Hi There Gurus - Another Replication question for you all:
Can anyone point me in the right direction to get info on using Linked Servers in Snapshot Replication?
We have Replication all scripted & working between Registered servers, but one of our divisions is not allowing us to register their box on the Parent - ONLY a Linked server.
However (the distributor) is not visible to Apollo2 so I am unable to subscribe.
Do you have any idea what I need to do to make it visible? I setup an ftp site to drop the publication (as I was unable to create an UNC Share) and I was hoping to be able to subscribe to it. Probably it is a matter of permissions...maybe a firewall? should I ask my provider to open a port? do I need to create the same 3 accounts in the Subscriber? something like Apollo2SubscriberUser?
