When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
i have replication setup and working, there are 3 subscribers and now i want to add a 4th one. is it ok if i just add the subscriber and reinitialize replication?
View 1 Replies View RelatedWe have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
For study effect, can I configure merge replication using just one SQL Server Instance?
View 3 Replies View RelatedI have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
View 0 Replies View RelatedWe have transaction replication set up on one of our servers whose replicated DB is used for reporting purpose:
Now, the replicated database "D" of size 350 GB has mainly grown huge as compared to Published DB (200 GB) because of the index sizes at subscribed DB. Therefore:
I have found that Database D has one table of data size 15 GB, with rows 8349533 and index size 28 GB the biggest in the database. And adding have total of 109 Non clustered indexes:
On using SP_blitzindex i have found approx 50 NC indexes unused with below usage:
Reads: 0 Writes:273,243 and total size of indexes being 18 GB
Last User Seek
Last User Scan
Last User Lookup all '0'Â
0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;Â
except for few 3 or 4 where updates are more than 4000.
Is this sufficient enough to delete all the above 49 unused NC indexes? And Can i create Missing index on Subscriber database?
I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.
ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR
[Code] .....
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
I have a central server (CS1) with database CDB and a branch server (BR1)
Inside the branch server, there are two database DB1 and DB2.
CDB contains data for DB1 and DB2
I made a subscription in the brancserver named BR1:DB1(this will replicate the data from CDB to DB1)
I also made a subscription for DB2 named BR1:DB2(this will replicate the data from CDB to DB2)
Unfortunately, upon starting the synchronization for BR1:DB1, the data replicated into DB1 also contains data for DB2. same happens with the BR1:DB2..
How do I filter the data that will be replicate per database..Example:only data for DB1 coming from CDB should be replicated into DB1 if that specific subscription(BR1:DB1) is synchronized.
Thank you
I have database (DB) on the server SQL1. This database (DB) is published on SQL1 server for SQL2 server. So, SQL1 is publisher and SQL2 is subscriber for (DB). Can I publish database (DB) on the server SQL2 for the server SQL3? I would like to implement this scheme of Publication/Subscription:
<o:p></o:p>
SQL1 -> SQL2 ->SQL3..
I know, that it is simpler just to publish DB for SQL2 and SQL3 from SQL1, but, because of network connectivity, I can't do that...
At my subscriber database I found some missing Indexes if I update all the missing Indexes on subscriber DB, does it impact at on publisher side?
View 2 Replies View RelatedI am doing replication through C#.net after creating the publication in server and subscription in client. But i want to know how can we view the transactions that are happening to the both databases through the C#.net code...If anybody knows please help me....
View 1 Replies View RelatedI'm in a project to replicate a database from the production environment to another sql server.
The idea is use it for some biggest querys because in the production database we had experimenting various problems. What is the best way for do it? Transactional replication? Log Shipping? I'm reading absolutely everything but I can not get decide.
Both servers are SQL Server 2008 R2 and they are in the same LAN.
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
I am getting "Row not found on subscriber" error on the subscriber. I am trying to find the root cause.
1. Records get lost during publication initialization from backup, could not reproduce it though. I was inserting records in a loop into a replicated table while initializing the publication from backup. After the backup/restore finished, I have stopped the insert, allowed the replication to catch up and compared the row count, it was identical.
2. Someone or some process is deleting the records on the subscriber. Setup Audit, but could not confirm this theory so far.
3. Records get deleted twice, as we publish the execution of some bulk delete archive jobs, to fix latency issues. Found this article, which says, this is not the case:
[URL]
4. Records are deleted on the subscriber by triggers. The impacted database has no triggers.
I am running SQL Server 2008 R2.
What else could be the cause of this problem?
Hi ..
I know that SQL Server Express 2005 can subscribet to SQL Server 2000 publications. However, it seems that SQL server 2000 cannot perform a push to SQL Express.
Can anyone refer me to any online help or documentation on how a proper replication can be done between SQL 2005 Exress and SQL 2000??
Thanks
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
Any experience of success or failure setting up CDC on the subscriber end of transactional replication?
Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:
• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.
I 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).
This application runs on a SQL Server 2008 R2 database.This application receives messages from an integration module. It has a core table: Table-A. Each message is inserted as 1 row into Table-A. Then when it is processed, that row in Table-A is updated.
There are two environments which are both connected to the same integration. So in both environments, Table-A has exactly the same amount of records inserted and updated. In both environments Table-A has around 80 million rows, with an extra 150,000 rows being inserted and then updated every day.Table-A has 8 indexes. For some reason unknown to me, the 8 indexes fragment really quickly in one environment but not in the other.
e.g. In Environment-1 the index fragmentation ranges from 0 - 19% and this environment has not been re-indexed for over 2 months.BUT a reindex was performed in Environment-2 and only 2 days later the index fragmentation ranges from 72 - 99.93%!
Our DBA has confirmed the re-index in Environment-2 completed successfully and has shown stats before and after the reindex to show that the 8 indexes for Table-A in Environment-2 went down to 0% fragmentation.
My question is, how can the indexes in Environment-2 fragment so much more quickly than the indexes in Environment-1? Both environments are on exactly the same hardware and have exactly the same inbound messages. The database on Environment-1 is actually a clone from Environment-2. The only known differences between the 2 databases is Environment-1 is STANDARD edition - SQL Server 2008 R2 (SP2) whereas Environment-2 is ENTERPRISE edition - SQL Server 2008 R2 (SP1). Could this difference be due to the Service Pack levels or even because one is STANDARD and the other ENTERPRISE?
This is what I have checked so far:
1) In both Environments all 8 indexes have "Set Fill Factor" unchecked and "Automatically recompute statistics", "Use row locks...", "Use page locks..." checked.
2) The "Index Usage Statistics" report in both Environments shows a similar amount of #UserUpdates and #UserScans
I am working with one of the production database around 200 GB. This database has above 350 tables and more than 500 Indexes. I am feeling the database has so many Indexes than the required ones
When I run the below query, it gives me some indexes read value "0". The server was restarted a month ago.Is it ok to remove those indexes?
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
[code]....
We have a maintenance plan that reorganize all indexes in a database. We disabled one of the index on one table this job failed. How to set up to the maintenance plan to without failing to ignore the reorganizing of disabled indexes?
View 1 Replies View RelatedWe have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
Somewhere i read..that in SQL Server...usage of Built-in Functions on Columns, makes query optimizer to ignore indexes on that column...!
So lets say we have table EMP with Emp_id and Emp_Name....
Also we have Non-Clustered index on Emp_Name.
So following query would NOT use Non-Clustered index on Emp_Name column.
SELECTLEFT(emp_name, 3) as emp3
FROMdbo.EMP
Is this true? i am using SQL Server 2008.
SQL Server 7.0 SP3
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________
mmc.exe has generated errors and will by closed by Windows.
You will need to restart the program.
An error log is being created.
__________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
Any help is appreciated.
Thanks,
Craig
Hi all!
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers.
Publisher:MSSQL 2000, SP3
Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when
synchronizing with the message
"This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I
append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image
column or different Service Pack Versions - definitely not
possible in this case!
The only strange detail is that the SQL Server Versionnumber
of this MSDE shown via EM is not the same as the number shown
in controlpanel->software. By the way, is it ok that here are
two lines for the MSDE?
SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope...
Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623
Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0
Verbindung mit Abonnent 'MyServerAT074'
Connecting to Abonnent 'MyServerAT074.MyDB'
MyServerAT074.MyDB: {call sp_MSgetversion }
Percent Complete: 0
Verbindung mit Verteiler 'MyServer1s'
Connecting to Verteiler 'MyServer1s.'
MyServer1s.: {call sp_MSgetversion }
MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') }
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServer1s'
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServerAT074'
MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB',
N'MyDB', 9, N'MyDB')}
Percent Complete: 0
Initialisiert
MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')}
Percent Complete: 1
Verbindung mit Verleger 'MyServer1s'
Connecting to Verleger 'MyServer1s.MyDB'
Connecting to Verleger 'MyServer1s.MyDB'
MyServer1s.MyDB: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'MyServerAT074.MyDB'
Disconnecting from Abonnent 'MyServerAT074'
Percent Complete: 4
Uploadet Datenänderungen zum Verleger
Percent Complete: 5
Ruft die Liste der Löschungen von MyServer1s.MyDB ab
Percent Complete: 5
Verarbeitet Artikel 'First'
......
SNIP
....
Percent Complete: 5
Verarbeitet Artikel 'LastTable'
Percent Complete: 0
Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen.
Percent Complete: 0
Category:SQLSERVER
Source: MyServerAT074
Number: 0
Message: Attributverletzung eingeschränkter Datentypen
Attributverletzung eingeschränkter Datentypen
Disconnecting from Abonnent 'MyServerAT074'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verteiler 'MyServer1s'
:confused:
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
Thoughts?
I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.
View 3 Replies View RelatedHi Guys,
I have tried backing up the replication from Primary Server (Publisher) A and copying over to Subscriber B. When I sync. I am missing the data. I don't know what step I am missing or it is bug on SQL 2K SP3.
I don't know what i am doing wrong.
Can anybody give me a step by step detail how to backup the publisher database and copy to a subscriber and sync by saying the schema and data already available. As we have WAN problem all the times, and when I try the full snapshot of 20 gig it was taking more than 24 hrs and also if the connection fails my snapshot also fails
Please help.
Thanks in Advance,
-Anu
Hi Guys,
I tried all the ways ,no good result.
I am really running out of time.
Can somebody please help.
-Anu
Hi there,
I installed 2 SQL2000 servers at a client site, the first acting as a publisher and distributor for transactional replication. The second is a push subscription to the first. The client wanted to change the database name, and some other stuff, so I removed the replication and set it up again for the new database name.
All's fine at the publisher / distributor but the subscriber is showing as still having active push subscription to the old (and new) databases
Any idea how to fix this