.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Partition set up in transactional replication SQL Server 2008

Posted By:      Posted Date: October 16, 2010    Points: 0   Category :Sql Server
Currently The partition strategy with switching works fine on a non replication enviornment.But I know there any few steps that needed to be considered before setting transaction replication.
I know we have to set 'allow_partition_switch' and replicate_partition_switch to true on the publication.
Inspite of setting them my replication fails while doing partition slide saying 'The <archived table> is not found'.
Any help is appreciated

View Complete Post

More Related Resource Links

SQL Server 2008 Cluster Settings for Transactional Replication


Is there anything I need to take care on a SQL Server 2008 Active/Active cluster (Publisher) Before setting transactional replication to non cluster enviornment(Subscriber)?


Transactional replication - SQL SERVER 2008 R2



We are planning to upgrade my DEV,QA and Production environments from SQLSERVER 2000 to SQL SERVER 2008 R2. And planning to implement the Transactional Replication in SQL SERVER 2008 for Production environment.

We have frequent releases to Production like monthly once. In those rleases we might be modifying the articles  (TABLE, VIEWS,SP,FUNCTIONS and TRIGGERS). Could you please let us know the best way to implement the Transactional REPLICATION and pre-requisites for this process.

We had faced some problems in SL SERVER 2000 for Transaction Replication. When ever we are using any ALTER TABLE scripts in this replication.The replication has broken.

Could you please provide the best practices if you have?

Thanks in advance




Sql Server 2008 R2 Replication

Dears, I am make a testing environment before implementing it into real world. I have 3 servers SQL 2008 R2, i configured 1 Publisher, 2 Subcribers with a merged replication. Everything is Ok. However, i have an ID field on Publisher which is autonumber(Start by 1, increment by 1). When I try to add new record on publisher, it takes it 1 by 1 i.e. 1-2-3.... But i try to add it on subscriber it starts with 20,000 i.e. 20001, 20002. I think this is logical for SQL, but can i define this number on the subscriber? So it will start on 200,000 for example? Another question, In LAN i can connect the subscriber to the publisher, it will connect to it directly, since it is broadcast. But in WAN environment how can the subscriber detects the publisher??? Your reply is highly appreciated, Thanks in advance,

Replication between SQL Server 2005 and SQL Server 2008

Hi,    We were using SQL Server Transactional Replication between two SQL Server 2005 servers. Now we are going to upgrade the Publisher server to SQL Server 2008, Distributor Server remains as SQL Server 2005.     Can anyone let me know what is the challenges we need to face when configuring Replication between SQL Server 2005 and SQL Server 2008. Replication Method - Transactional Replication Distributor/Subscriber - SQL Server 2005 Publisher     -  SQL Server 208 Regards, Fazlu.

sql server 2008 Table Partition

HI i want to implement partitioning in my sql server 2008 database table http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx   i have used this link. but when i try this link then i am able to create only primary file group no secondary file group..so help me also by fault i have created partition in wrong table .so i want to detete tha partition from that table but of i delete the PARTITION SCHEME and PARTITION FUNCTION THEN it throws error..so what shoul i do to remove partition from my current table

sql server 2008 Table Partition

HI http://blogs.msdn.com/b/manisblog/archive/2009/01/18/easy-table-partitions-with-sql-server-2008.aspx  i have used  above link to create partition,but my problem is i want to do partition on daily basic, one column in my table is date(by default has getdate()),on the base of this i want to do partition, eg.in this link i need to give time duration between which one can do partition, and also either daily or monthly or quarterly or yearly need to be specify ..if i give date between 01/08/2010 to 01/08/2012 and then select daily wise partition which i am in need then table create more than 700 partion but problem comes when i need to assign group to them how cud i select Primary file group more than 700 times to assign it to each partiotion.it becomes very lengthy process  Please help me

Partition sql server 2008

I am designing database for fleet management system. I will be getting n number of records every 3 seconds. Obviously, there will be millions of record in my table where I am going to store current Information of vehicle in the current_location table. Here performance is an BIG issue. To solve this, I received the following suggestions: Create an septate table for each vehicle. Here a table will be created at a run time as as soon as I click on create new table.And all the data related to perticular table will be inserted and retrive from that perticular table Go for partition. Please answer the following questions about these solutions. What is difference between the two? Which is best and why? At what point will the number of rows in the tables cause performance issues? Are there any other solutions? Now ---if I go for range partition in sql server 2008 what should i do to, partition useing varchar(20). i am planning to do partition based on vehicle no.eg MH30 q 1234. Here In vehicle no. lets say mh30 q 1234--only 30 & q going to change....so my question is HOW SHOULD I GO. means how should write the partition function.

Using SQL Server Replication for SQL Server 2005 and 2008

I have an ASP.NET application that uses replication, and uses the RMO DLLs to do this. I initially built the application against the 2005 version of the DLLs and have it running successfully against SQL Server 2005. However, when I try running it against SQL Server 2008 it doesn't work. Initially I didn't deploy the Microsoft.SqlServer.* DLLs with my app - instead relying on them being in place on the server on which it was installed. However, when trying to make it work against SQL Server 2008 it was looking for the 2005 version DLLs which it wasn't able to locate. Following some other advice I read online I have now swapped out the 2005 DLLs and referenced the 2008 assemblies in my .NET project. In my MSI I now also deploy (i.e. redistribute) the SQL Server 2008 DLLs to the bin folder of my ASP.NET project. Now, when I try to run the application against SQL Server 2005 it fails to load the Microsoft.SqlServer.Replication assembly. I run depends.exe against this and find that it's dependent on SQLNCLI10.DLL which is missing on my machine with only SQL Server 2005 on it. So, what am I to do? Is this possible? How do I get around this?

SQL server 2008 to oracle replication

Hi, I am trying to replicate data from a sqlserver to an oracle 11 subscriber(push subscription). I recently moved to SQL server 2008 from 2005. The replication in 2005 worked ok, and now in 2008 - it does not. The problem: It tries to make some fields as an interval data type in Oracle. For example, nvarchar(3) are turned into interval and numeric(38,8) fields are turned into nvarchar2(40). Anyone has any idea why is this happening and how can I change it?

SQL Server 2008 Standard replication

Dear All, Im new in SQL, would like to what will be the consideration and effort from [WIndows Server 2008 STd with SQL Server 2008 STd] and replicate over to another location with same setup? Is there any guide which could describe the steps for this?  

General replication questions - SQL Server 2008 R2


First, can SQL Server 2008 R2 replicate to SQL Server 2008 R2 Express Edition???

Second, I have setup replication from R2 to a SQL Server 2000 database.  I defined a stand-alone table (not a parent or child) and replication worked fine.  I simply changed a record and it was reflected in the 2000 database.

Next, I changed the table to be replicated to be a parent table.  After I changed a record in this table, replication failed.  The error message said it was unable to drop a table because it was in a relationship.

Questions.  When replication occurs between a publisher and subscriber and several records in a table targeted for replication, is the table dropped in the subscriber database then copied over in its entirety???  When I define a table to be replicated, do I have to include all the related tables???



SQL Server 2008 SP1 - Replication


Hello All !

I have some questions about replication with SQL Server 2008 --> context

An offshore team has developped a Web application (IIS 7/.Net 3.5). The databases are stored on volumes hosted on a SAN (Windows 2008 cluster 2 nodes with SQL 2008 SP1 in A/P mode)

Special queries and results (insert/update/delete) are stored in database on cluster

Special consultation queries (select only) are redirected to another server which will contain the database copy) in order to create realtime reports

Log shipping and mirroring can't be choosed because this application needs real time.

I want to know if it is possible to create a solution with cluster and replication. If yes, waht is the prerequisites and how to do that ?

Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2

SQL Server 2008 merge replication snapshot hangs on filtered articles


I have a publication on SQL Server 2008 Standard Edition using merge replication.  When I attempt to generate the initial snapshot, the snapshot agent appears to hang on the step "Setting up the publication for filtered articles."  I get a long (over 4 hour) series of messages: "The process is running and is waiting for a response from the server."  I know something is happening server-side, as SQL Server and the snapshot agent use a lot of memory and max out one core's processing capacity.

This has me confused as the publication is not doing any filtering.

Even more confusing:  I backed up the database and restored it onto my development-test system.  I created the snapshot there, and it took under 10 minutes every time.

Any suggestions for investigating and resolving this?

SQL Server 2008 Merge Replication article triggers not replicating


Currently running merge replication on SQL Server 2008 10.0.1600 and having issues with adding and modifying triggers on published articles.  I've searched through the MSDN site and googled it but have not found any definitive information on correct procecdures.

We have a replicated table that had 1 trigger on it when it was added to the publication.  That trigger was replicated.  We have since added a 2nd trigger to the table and it is not being replicated to our other server.  I've checked the Article Properties and Copy User Triggers is set to TRUE.  The only difference is the new trigger has NOT FOR REPLICATION in it, but I thought that would only keep it from being executed if a row is effected by the MERGE process.

We have rerun the snapshot and are syncing continuosly and the trigger does not replicate.  Do I have to drop the tablefrom the publication and then re-add it?  Am I missing something? 


SQL SERVER 2008 Replication Databases


HI all,

I Need to configure Merge Replication, in this replication Can i chose the Replication Databases Recove model are Simple is there any problem or not, why i am asking this question is, every time my log file size is become very large nearly 250GB. 

Please help me

Thanks, Prasad.  

SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp


Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

Setting Publisher as distributor also in Transactional replication from SQL 2008 to SQL 2008


Can some one please tell how will the performance of the Publisher be affected if I use the same server as the distributor ? How can i get the best performance from Publisher in case i use this as distributor as well ?


any ideas or links  would be appreciated 




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend