I'm beginning to wonder if replication is worth the trouble I've had to spend on it!
Trying to create a simple peer-to-peer replication topology. I can create the initial publication. I can change the property to allow P2P. I can backup the database and I can create a snapshot. When, however, I try to configure the peer-to-peer topology, I get a 14027 error message telling me that ONE table out of 250, or so, "does not exist in the current database." That's pretty troubling given that I can run a select query against that specific table. I would appreciate any information that would help me to understand what's going on or successfully set up replication.
We have three geographically dispersed offices, each with their own local SQL Server. We are creating a DB application (SQL Server 2005) for our primary business (insurance) and expect to have one database with replicas on the other servers. (There is no "primary" or home server -- each of the local replicas/servers are peers.) Users in each office will update records in the local replica of the database for clients principally served out of that office. All data, however, must appear on all replicas for business intelligence purposes. In addition, there will be some (though limited) amount of record upating in one office for clients principally served out of another office. The nature of the business is such that it is unlikely users in two different offices would be working on the same record at any one time. It is not unreasonable, however, that during the course of the day, two users in different offices might edit the same record. Therefore, data inserted/updated in one server should be replicated to the other two servers within no more than an hour or so.
Given the above, how should I approach replication? The way I read the MS documentation, I should be looking at peer-to-peer merge replication. As an alternative, I might also use a peer-to-peer topology with a transactional replication. In the latter case, however, I should be looking at updating subscribers. Some here have suggested that neither approach is necessary and that I can use plain vanilla peer-to-peer replication with straight transactional updating (or I have completely misunderstood their posts). Because there is no central server, creating "non-peer" publishers on each of the local servers doesn't seem to be an option. As a final twist, so far as I have been able to figure out, MS SQL Server Studio 2005 will not allow me to create peer-to-peer replication with anything other than straight, non-updatable, updating -- With transactional plus updating subscribers the option to enable peer-to-peer is greyed out and the menu entry to configure peer-to-peer topology does not exist. With merge replication, the option does not even exist (on the GUI) and, again, the menu entry is missing.
Any thoughts, help, comments or criticisms on this subject would be most appreciated.
I have a replicated database on 2008 R2 being moved to a new instance with 2014. Application regression testing is complete and all tests are good.
The current transactional replication topology is Publishing subscriber: the distributor is local and pushes to a subscription on an intermediary server. This server then has an identical publication and local distributor which then pushes to a subscription on a customer's server. There's no business case for this setup, it's just how it was created at the time.
I want to change this to a Central Distributor model when we do the move to speed things up and remove any extra potential points of failure.
The customer has a requirement that during this process, their subscription database remain online, all tables are fully populated and replication pick up exactly where it was left off. This is a completely new scenario for me and I was hoping to just be able to rebuild the subscription from scratch.
Is there a way to do this move and topology change and meet the customer's requirement?
I am given with the responsibility to set up database replication in my organisation.
Before starting the replication set up, I would like to know which is the best suited replication for my application.
My application is much process oriented, and the users are working on records parallely(but with different records).
Say for ex:
There is a module for Insurance claim processing, it is handled in the following manner:
There are many employees with CSR roles, and all are working on some set of records to be processed. But No two employees should get the same record for processing. For that, we are doing locking of the cases by having one column called 'LOCKED_STATUS' and setting value as LOCKED. So that other users wont get the same case. Once the case is processed, the record is marked as unlocked.
My analysis for replication is as follows:
1. Snapshot replication: This wont be a suitable type of replication as the updations are to be immediately updated on all the subscribers, because when the user executes select query to get the next unlocked case, No 2 user should be able to get the same case.
I think, the best replication to chose from is, Transactional replication with Immediate Updation/Queued updation. But the problem is, when one of the subscriber goes offline, the updations are queued up. But in my case, say for ex. One case is updated as locked, but the subscriber is offline and the updations are queued up. At the same time, the other subscriber can fire an select query to get the next unlocked case. Since the update is on queue, there are possibilies for this subscriber to get
the same case.
Can anyone let me know, are the select statements also queued up(i.e., along with update and insert statements.)? If not please let me know how to approach for this problem.
I have one publicacion on ms sql 2005 Enterprise w.sp1/MS Windows 2003 Enterprise w.sp2 and one subscriber on MS sql 2005 Enterprise w.sp1/MS Windows 2003 w.sp2, but I have also 3 subscriber on MS SQL 2000 w.sp3/MS Windows 2000 advanced server w.sp4. They replicate the same database with a publication compatible with 80RTM, All works fine with data replication, but I can't replicate DDL to MS sql 2005 suscriber, I don't care that don't replicate DDL, but I want manually alter triggers at MS sql 2005 suscriber, but I get this error at the subscriber on when I run the ALTER TRIGGER statement :
Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertrigger, Line 67
The DDL statement cannot be performed at the Subscriber or Republisher.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.
Msg 3609, Level 16, State 2, Procedure TU_Centros_Distribucion_Articulos, Line 58
The transaction ended in the trigger. The batch has been aborted.
Any help will be appreciated,
PD. I miss sql2000 replication, where I have full control over the database logic
Hi every1, I have problem about configuration in Merge Replication. First, I configure for SQL Server 2000, expand Replication> Publication>New Publication>Choose Publication Database, I choose pubs>Select Publication Type, choose Merge Replication>Specify Subscribers Type, choose Servers running SQL Server 2000 and Devices running SQL Server CE>Specify article (ex. authors)€¦>Finish. I configure succeeded (A snapshot of 1 article(s) is generated). Snapshot folder reference to path: \server_nameC$Program FilesMicrosoft SQL Server 2005 Mobile EditionServerReplication Next, I configure for SQL Server Mobile Agent, Subscriber Type, choose SQL Server Mobile Edition>Web server, choose Create a new virtual directory>Alias: Replication (reference to the path: C:Program FilesMicrosoft SQL Server 2005 Mobile EditionServerReplication)..>Client Authentication, choose Client will connect anonymously€¦.>Snapshot share access, enter \server_nameReplication =>error: The share '\server_nameReplication' cannot be accessed. Please specify a new share. Why that? I want to ask u I configure that correctly? If Subscriber is Pocket PC Emulator, what is subscriber €™s property of SqlCeReplication (Windows PocketPC Emulator?)? Plz help me. Thanks so much.
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)
Hi, In a mapping I have two look up€™s before setting the configure error output €śERROR€? as ignore failure, the job was stopped with an error message €śRow yield no match€?. Now that I had changed the error as ignore failure the job is successful but only concern here is will the destination table get inserted with those records which are not matched in look up.
i want to upload reports in ssrs , when i trying to configure reporting service configuration manager, getting error" there was an error while switching panels. The most likely cause is an error retrieving wmi properties. the exception details are access is denied . You must have an administrator on the report server computer to use this tool".
i'm getting the following error message when i attempt to configure my server as a local distributor:
TITLE: Connect to Server
------------------------------
Cannot connect to 9A6375C3046246B.
------------------------------
ADDITIONAL INFORMATION:
Failed to connect to server 9A6375C3046246B. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
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. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
this error is occuring despite the fact that i have the server configured to allow remote connections. can someone please help me troubleshoot this error? thanks.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error occurred during decryption. There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_distributor'. Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2153&EvtSrc=MSSQLServer&EvtID=15466&LinkId=20476
Error message: Setup failed to configure the server
I'm attempting to install SQL Server 2000 Developer Edition on a 2nd workstation in our area for development and practice with having this 2nd workstation as a "local" (test) SQL Server box. The install fails with the above mentioned message. (We have a database running in production on SQL Server 2000 and we need a place to simply copy the database and run tests on it)
The target PC is running WInXP Pro-SP3 and has IIS installed, along with Visual Studio 2005 & .Net framework, etc.
I downloaded it (via MSDN license) and accepted the defaults on the install.
I installed Micrsoft Server Desktop Engine from the CD I received in my ASP.net book. The installation went fine and then I went to the DOS prompt and did the "setup SAPWD..." and it ran. I restarted my computer and opened ASP Matrix and went to the Data tab and proceed to connect to the server. It gave me an error message it couldn't connect.
So I uninstalled MSDE and made sure the sql2ksp3 folder was gone. So I went ahead and installed the MSDE again from the CD again and then went to DOS to do the setup. It ran through the setup until the very end with 11 seconds remaining and received error message " Setup failed to configure the server. Refer to the server error logs and setup logs for more information. I have tried it several times and it always stops around 11 seconds. I have tried to install the download from microsoft.com instead the CD and that didn't work also. So at this point I am stuck. PLEASE HELP!
I also checked my Registry keys for a file called PendingFileRenameOperations and it wasn't in the registry keys.
If anyone has any suggestions I would appreciate it.
I've tried looking for a solution in other threads but I couldn't find anything that was completely the same as my situation. I got a new pc at work and I've tried installing SQL Server 2005 on it but everytime I try to install it, regardless of services included or excluded with the install, I cannot get through the install without this error. Below is the excerpt from the Summary.txt log file.
Error Code: -2147024894 MSI (s) (74!98) [17:10:55:858]: Product: Microsoft SQL Server 2005 Tools -- Error 29549. Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894 Error message: The system cannot find the file specified. Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.
Error 29549. Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2147024894 Error message: The system cannot find the file specified. Error description: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. <Func Name='GetCAContext'> <EndFunc Name='GetCAContext' Return='T' GetLastError='203'> Doing Action: Do_sqlAssemblyRegSvcs PerfTime Start: Do_sqlAssemblyRegSvcs : Mon Nov 12 17:10:55 2007 <Func Name='Do_sqlAssemblyRegSvcs'> Failed to install and configure assemblies C:Program FilesMicrosoft SQL Server90NotificationServices9.0.242Binmicrosoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: 2147942402 Error message: The system cannot find the file specified. Error descrition: Could not load file or assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified. Error Code: -2147024894 Windows Error Text: The system cannot find the file specified. Source File Name: sqlcasqlassembly.cpp Compiler Timestamp: Thu Aug 11 01:12:01 2005 Function Name: Do_sqlAssemblyRegSvcs Source Line Number: 155
I've searched for and found similar problems that deal with the microsoft.sqlserver.notificationservices.dll not being found but I haven't found a fix that worked for me. These are the fixes I've refered to thus far.
The answer that seems to fix this problem for everyone is to set the MSDTC service login as the NT AUTHORITYNetworkService account. The problem for me is that this is already set to this profile and I've tried resetting the services and rebooting the machine. I did not have a problem with my old machine, which as far as I can tell, is the same configuration, so I know this install should work.
I'm running Windows XP SP2. This is not the upgrade to SQL Server 2005 SP2, this is the original disk that came with Visual Studio 2005.
Has anyone else faced this issue where changing the MSDTC login wasn't the solution? Also, this is my first post, so I apologize if I've left anything out. Just let me know if you want any other information.
Guys , I have a dedicated server ... I've installed SQL SERVER 2005 and I have a problem to connect to SQL from local machines in the office ... Remote connections are on , server BRowser starts automatically ... for example from remote machinne I mean from Server where SQL is installed I can connect to SQL ... FireWall i've turned off.... What can be the problem ????
Hi, I got a website and everything works fine with SQL Server 2000, but now I modified the way of authentication using Forms and when I enter in a protected area I got this error: An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by thefact that under the default settings SQL Server does not allow remoteconnections. (provider: Named Pipes Provider, error: 40 - Could notopen a connection to SQL Server) The problem is that I don´t have SQL Server 2005 installed so I don´t know why my webapp is trying to contact it, where I can configure this settings so it will look for the Server that exists? Thanks.
In my DAL, I have 3 tables - Footballer (FootballerID = Primary Key) Aspnet Membership Table (UserID=Primary Key) Favourite Footballers (Junction Table with 2 Primary Keys FootballerID and UserID - both with foreign relationships to their respective data table)
On my sports site, visitors can view various footballers and store their favourites in th FavouriteFootballers Table by clicking on a button. This stores the Logged in UserID and FootballerID in the junction table. I now need to write a select query that allows users to click a link and view their favourites which they stored - lets say in a data list on another aspx page. This would include other columns from the Footballer Table such as Name, Age, Profile ect I think I am right in that I need to use a Select clause as I am using SQl data adapters in the DAL. Be very pleased if someone knows how to contruct the query and give me an example how to code a method in the BLL Some code is below
Method that Stores FootballerID and UserIDprotected void DataList1_ItemCommand(object source, DataListCommandEventArgs e) {
// Assuming only have one button in the item templateInt32 fid = Int32.Parse(e.CommandArgument.ToString());Guid userid = (Guid)Membership.GetUser().ProviderUserKey;
// Connect to the db and insert the recordusing (SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True")) {
string query = string.Format("INSERT INTO FavouriteFootballer(UserID, ArtistID) VALUES('{0}',{1})", userid, fid); SqlCommand cmd = new SqlCommand(query, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); }
Code in FavouriteFootballersBLL Its not complete -withotu parameter - i think maybe I also need to reference the Footballer Table and not the Junction one [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]public DataSet1.FavouriteFootballerDataTable GetFootballerFavourites() { return Adapter.GetFootballerFavourites(); }
Hi, I am really having a hard time creating a parameter for the RadioButtonList radSearchField. I want to type 's' in the text the choose the column for the query to execute the Select statement. I was able to create a parameter for the txtSearchText but can't get the radSearchField working. My form contains four objects.
Textbox to perform the query. (name: txtSearchText)
We've A2k db which we plan to migrate to SQL 7.0 very soon. Though am familiar with upsizing wizard BUT don't know how to configure Access forms, reports etc. to talk to SQL back end tables. So that information entered via Access forms can be stored in sql and reports can be generated using sql tables..
Hi, I'm using Exchange server for our mail communication. If wanted to config SQL Mail in SQL Server 2000 is Ok 1. Insall outlook in sql server 2. US Exchange mail server name in sql mail or any procedure I need to do? Thanks, Ravi
What are the steps in configuring sql server 6.5 to have excellent performance? what are the items that I should think of and based on what condition I can set up the configuration?
Hello, I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that. I am confused here about which RAID should I install on this server RAID 1 or RAID 5. The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5
I cannot create a Distribution Server on SQL Server 2005 SP1. When I right click on Replication and click "Configure Distribution" and hour glass appears for a minute, and then nothing happens.
I get the same result if I try to create a publication.
Hi All,Here is my questionWhat might you configure on a SQL Server so that a Web server runningon the same machine, with limited memory, will continue to performoptimally?Thank youRaju
I have installed SP2 on my SQL 2005 instance which also houses RS of course. Additionally I installed WSS3.0 without configuring it so it has the Object Model installed (should be enough?)
I am running WSS 3.0 on a seperate Server and have the Add-in installed on there.
Following the installation instructions I am going to Application Management to configure WSS, but there is no section called "Reporting Services". Looking on the Report Server, in the Configuration I see the following error under the SharePoint Integration Setting:
The report server cannot access settings in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model is not installed or the Report Server Web Service and Windows service accounts do not have access to SharePoint databases. To configure service account access, use SharePoint Central Administration.
In my SharePoint Site I do see a SharePoint section under the "Site Settings" for managing shared schedules but I think the main issue is, that I don't see that section in Central Administration.
We have an 8gb SQL server running Windows Server 2003 Enterprise Edition that simply will not get above 3gb of memory usage. We are using SQL 2000 Enterprise edition, version 2040 - SP4. My boot.ini has been configured with /3gb /PAE. System Properties does list Physical Address Extension as being enabled. AWE is enabled, and has been verified in the SQL server logs. Lock pages in memory access has been given to the account SQL is started under. Under task manager, the process sqlservr.exe does not grow above 2,762,428 ish. Does anyone know why this is occuring?
