Consolidation - Changing Replicated Data In A Central Subscribing Site

Sep 25, 2006

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

View 3 Replies


ADVERTISEMENT

Changing Indexes In Replicated Tables

Dec 19, 2001

I need to drop and recreate indexes in some of my tables that are currently been replicated. I am not sure how this will affect my ongoing replication. Will this cause a problem for me? Please help

Bright

View 2 Replies View Related

Changing A Primary Key In A Replicated Table

Jul 20, 2006

Hi

We have a merge publication - I want to change the primary key for one of the tables (add another column to the primary key)

How do I do it ?

Currently there is no data in the table - which I guess might help..

thanks
Bruce

View 3 Replies View Related

Changing Table Props On Replicated Tables

Dec 13, 2001

How can I change a field size that is set to numerical 9,2 to 9,3? I need to allow 3 decimal places.

Thank you,
Kameron

View 2 Replies View Related

Changing Column Length Of A Replicated Table

Jul 31, 2006



Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

View 6 Replies View Related

Data Marts Consolidation

Sep 28, 2007

Hi guys, can you please tell me what are steps we need to take in order to consolidate two datamarts into one? My issue us that we have two datamarts and the client wants the data in these two marts to be in one mart. Please help. I will be grateful if you refer me to any documentation if possible.

View 1 Replies View Related

Replication :: Replicate All Data From Remote Locations To Central Location

Aug 29, 2015

there are several remote locations where sql is running, my company has asked me to find a way to collect all the data from the remote locations to a central location automatically,for example day to day data should be synced at night time from 2am to 7 am and it should be compressed automatically before data transfers to the central location. NOTE there is no domain only standalone workstations

View 3 Replies View Related

Replication :: Replicate Data From 3 Publishers To A Single / Central Subscriber Transactionally?

Oct 15, 2015

Is it possible to replicate data from 3 publishers to a single/central subscriber transactionally? In other words I have Server A, Server B, Server C with databases A,B,C respectively. I need to replicate 2 articles from A,2 from B and 2 from C to a central Server D that hosts database D. D will have only 6 articles. The replication is Transactional Replication.

If it is possible what will be the drawbacks of such implementation? (if one server goes down will the whole replication break?) If not possible then what is the best way of implementing this?

View 3 Replies View Related

Collect Missing Index Data Into Central Repository From Sys.dm_db_missing_index_group_stats, Groups, Details

Nov 2, 2007



Greetings:
I am trying to gather into a central location the missing index data from the sys DMV's for dynamic index creation in the next step. In trying to use a cursor, I get the following errors:

Msg 154, Level 15, State 3, Line 20

variable assignment is not allowed in a cursor declaration.

Msg 102, Level 15, State 1, Line 94

Incorrect syntax near 'Get_Data'.

Msg 16916, Level 16, State 1, Line 2

A cursor with the name 'Get_Server' does not exist.

Msg 16916, Level 16, State 1, Line 3

A cursor with the name 'Get_Server' does not exist.


Here is the SQL:


--CREATE PROCEDURE usp_Get_Missing_Index_Data

--AS

--Declare @Sql2 nvarchar(4000)

Declare @Sql nvarchar(4000)

DECLARE Get_Server Cursor -- gets a server name from a list of servers

for

Select MachineName from rsqlaudit1.DBStatistics.dbo.servers

Open Get_Server

Declare @Server nchar(20)

Fetch Next from Get_Server Into

@Server

While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)

BEGIN



DECLARE Get_Data Cursor

FOR

select @sql= 'select distinct id.*

, gs.avg_total_user_cost

, gs.avg_user_impact

, gs.last_user_seek

,gs.unique_compiles

from '+@Server+'.master.sys.dm_db_missing_index_group_stats gs

,'+@Server+'.master.sys.dm_db_missing_index_groups g

,'+@Server+'.master.sys.dm_db_missing_index_details id

where gs.group_handle = g.index_group_handle

and id.index_handle = g.index_handle

order by gs.avg_user_impact desc'

exec (@Sql)



Open Get_Data

DECLARE @Handle int,

@database smallint,

@object int,

@equality nvarchar(4000),

@inequality nvarchar(4000),

@Included nvarchar(4000),

@statement nvarchar(4000),

@avg_user_cost float,

@avg_user_impact float,

@last_seek datetime,

@compiles bigint

Fetch NEXT FROM Get_Data INTO

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles

While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)

BEGIN

insert into rsqlaudit1.DBStatistics.dbo.Missing_Index_data

values (@Server,

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles)

FETCH NEXT FROM Get_Data into

@Server,

@Handle,

@database,

@object,

@equality,

@inequality,

@Included,

@statement,

@avg_user_cost,

@avg_user_impact,

@last_seek,

@compiles

Fetch Next from Get_Server Into

@Server

END

CLOSE Get_Data

DEALLOCATE Get_Data

GO

CLOSE Get_Server

DEALLOCATE Get_Server

GO

Any suggestions are appreciated.

Thanks,
Derek

View 3 Replies View Related

How To Tell When Data Has Been Replicated?

May 1, 2007

Hello,



In our environment (SQL 2005) we have a database that uses Transactional Replication to sync data between two SQL 2005 servers. There is a web app that reads/writes data to the publisher server and the other server (that gets the replicated data) is used by some other internal applications.

At times, there is a need to delete some data from the publisher server...but this can ONLY happen once the data has been successfully replicated to the second server. Is there any way to determine if a row has been replicated successfully?



thanks

- will

View 4 Replies View Related

Changes To Data Not Replicated

Jan 21, 2007

I have an application that uses web-based merge replication. My publisher is SQL 2005 and my subscriber is SQL 2005 Express. I control the replication with RMO code. If I make changes to the data in both databases using SQL Server Management Studio Express, my RMO code correctly syncs the two databases. However if I make changes to the data at the subscription through my application, these changes are not picked up by the replication process, even though the changes are present if you check the tables through Management Studio. What would cause these changes to not be recognized? Any ideas would be appreciated.

View 13 Replies View Related

Subscribing The Report

Jul 4, 2007

Hi !



I am subscribing the report from the report manager in File Share mode.But the Report is not getting stored in the specified location after the Schedule time. This would be the problem the with Reporting Configuration,Because the trigger run status is blank in report manager.Could anyone help me out in this issue.

View 3 Replies View Related

Size Of Data Replicated???

Jan 17, 2007

hi,

can anybody advice me how to find the size of the data replicated every minute. is there and procedure for getting the size of replicated data.



Thanks in advance

Jacx

View 1 Replies View Related

Subscribing To A SQL Report In Sharepoint

Mar 19, 2008

Hello,


I have a report in sql that i would like users in sharepoint to be able to subscribe to, however when i try to subscribe to the report i get the following error -

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting).

Any ideas?

Many thanks.


View 5 Replies View Related

Subscribing To A Sql Report Using Sharepoint

Mar 25, 2008

Hello,


I have a report in sql that i would like users in sharepoint to be able to subscribe to, however when i try to subscribe to the report i get the following error -

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting).

Any ideas how i could solve this error?

Any help would be much appreciated, thank you.

View 11 Replies View Related

Subscribing To Two Publications On SQLCE

Nov 27, 2006

I have two publications on a SQL Server 2000 database.
I am able to create two subscriptions from another SQL Server 2000 database
and synchronize both in succession.

However when I try to repeat this going from SQL Server 2000 to SQLCE 2.0
it fails. The first goes OK. The second fails.
I get error 80004005, 28521 (The SQL Server CE database is already enabled for publication.
Is it possible to do what I am trying to do on CE?

The reason I have 2 publications is because the first is non-filtered and goes very fast via bcp files when reinitialized.
The second is dynamically filtered and not as fast. Breaking them up makes reinits go much faster.

My code is included below.

Thanks
Ed Santosusso
esantosusso@omicron.com

--------------------------------------------------------

// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge);
CEMerge->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge->put_InternetLogin(L"<userid>");
CEMerge->put_InternetPassword(L"<password>");
CEMerge->put_Publisher(L"mysqlserver");
CEMerge->put_PublisherDatabase(L"MyDataBase");

CEMerge->put_Publication(L"Publication1");
CEMerge->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_PublisherLogin(L"sa");
CEMerge->put_PublisherPassword(L"mypassword");
CEMerge->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_Subscriber(L"TestReplAndMerge");
CEMerge->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge->put_Distributor(L"mysqlserver");
CEMerge->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge->put_DistributorLogin(L"sa");
CEMerge->put_DistributorPassword(L"mypassword");
CEMerge->put_HostName(L"some host name");
CEMerge->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge->put_Validate(NO_VALIDATION);


bool NewFile = false;

// see if we need a file


if (FileExists(L"\mydatabase.sdf") == false)
{
hr = CEMerge->AddSubscription(CREATE_DATABASE);

if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge);
return 0;
}
NewFile = true;
}


hr = CEMerge->Initialize();

if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Initialize failed",CEMerge);
return 0;
}

hr = CEMerge->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Static Merge Run failed",CEMerge);
return 0;
}


// Destroy the Replication object
if (CEMerge)
CEMerge->Release();



// set up for Second Merge

// Create and initialize the Replication object
hr = CoCreateInstance(CLSID_Replication, NULL, CLSCTX_INPROC_SERVER, IID_ISSCEMerge, (LPVOID *)&CEMerge2);
CEMerge2->put_InternetURL(L"http://myiisserver/sqlce20/sscesa20.dll");
CEMerge2->put_InternetLogin(L"<userid>");
CEMerge2->put_InternetPassword(L"<password>");
CEMerge2->put_Publisher(L"mysqlserver");
CEMerge2->put_PublisherDatabase(L"MyDataBase");

CEMerge2->put_Publication(L"Publication2");
CEMerge2->put_PublisherSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_PublisherLogin(L"sa");
CEMerge2->put_PublisherPassword(L"mypassword");
CEMerge2->put_PublisherNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_Subscriber(L"TestReplAndMerge");
CEMerge2->put_SubscriberConnectionString
(L"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\mydatabase.sdf");
CEMerge2->put_Distributor(L"mysqlserver");
CEMerge2->put_DistributorNetwork((REPL_NETWORK_TYPE)DEFAULT_NETWORK);
CEMerge2->put_DistributorSecurityMode((REPL_SECURITY_TYPE)DB_AUTHENTICATION);
CEMerge2->put_DistributorLogin(L"sa");
CEMerge2->put_DistributorPassword(L"mypassword");
CEMerge2->put_HostName(L"some host name");
CEMerge2->put_ExchangeType((REPL_EXCHANGE_TYPE)BIDIRECTIONAL);
CEMerge2->put_Validate(NO_VALIDATION);


if (NewFile)
{
hr = CEMerge2->AddSubscription(EXISTING_DATABASE);

if (!SUCCEEDED(hr))
{
ShowMergeErrors(L"Add Subscription failed",CEMerge2);
return 0;
}
}


hr = CEMerge2->Initialize();

if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Initialize failed",CEMerge2);
return 0;
}

hr = CEMerge2->Run();
if (!SUCCEEDED(hr)) {
ShowMergeErrors(L"Tech Merge Run failed",CEMerge2);
return 0;
}


// Destroy the Replication object
if (CEMerge2)
CEMerge2->Release();


View 1 Replies View Related

SQL Consolidation

May 18, 2006

All,

I have seven SQL servers with 1 or 2 databases on each. I need to move the databases and consolidate them down to one or two servers. I am new to this and hear about detaching and attaching the databases. Is this the way to go? If so, can someone tell me how. I am sure it's not as simple as copying the way things are done in the Windows environment.

Thanks

View 4 Replies View Related

SQL Server 2008 :: Data Is Not Getting Replicated

Jun 24, 2015

We are using push subscription using transactional replication. Is there a recommended value for retention period on distributionDb? We are using default value of 72 hrs and recently we saw an issue where data was not replicated with an error that subscription was inactive. When I searched, I fid that it is related to the retention period setting on distribution DB.

View 0 Replies View Related

Subscribing Report For Time Intervals

Mar 5, 2008

Hi there,

Is there anyway of subscribing to a report to run for every 1 hour between the times off 9am to 5pm only ?

I cant seem to find the ability to set time intervals anywhere

View 1 Replies View Related

DB Backup Consolidation

Aug 13, 2007

Hi All

I need some advice and I think I have a possible solution, I just need some approval that it is the right thing to do!

I have 73 Databases which I look after and they are all SQL 2000 and SQL 2005 DBs and backuing up these DBs is becoming an Administration nightmare. So what I was planning is to recreate all the backup jobs jobs on a SQL 2005 Server and monitor them from there!

Is this a good idea or should I think of something else?

Thanks In Advance!

Gopher

View 8 Replies View Related

Sql 2005 Consolidation

Dec 20, 2007





Can anyone point me in the direction of some best practice resources for consolidating Sql 2005 on a 64 but Unisys platform?

The web seems a bit sparse unfortunately and most of the best practice information seems oriented around 2000. This is a great article but Im not sure how much has changed in 2005 http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx .

View 4 Replies View Related

Consolidation Of Sub Packages

Apr 14, 2008

here is the idea,
we are trying to automate our weekly import process. we are running the sub packages with it own configuration file in sequence. i want to integrate the process in to a main package ?

i also want to create a job to run this main package.
do i need any configuration file for the main package?

i know how to do it in sql2000 but i don't know in sql2005. can anyone point me in the right direction that would be great

thank you so much !

View 4 Replies View Related

Consolidation Of Packages

May 21, 2008



Hi

I have

Package1 and Configfile1
Package2 and Configfile2
and so on...

Currently i am running it manually one by one on SMS using integration services.
I have assigned to automate the process or consolidate into a single package.
I can automate it using execute package tasks without config file. there is no option
in the execute package task to point to the right config file.

Any pointers on this would be appreciated.

View 3 Replies View Related

How To Alter The Data Type In A Replicated Databas

Jun 11, 2008

Dear All,
i've one database replicated from production server.
now i need to change one perticular table column datatype.
what steps i need to follow to do this?

thankyou very much

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 13 Replies View Related

Manually Input Data Into Replicated Table

Jul 17, 2007

Is it possible to have a replicated table that you can open from and input data manually?

That is, I've got a table that I am replicating data to and I want to add some manual data to this table, every now and then.

Will this break replication?


www.beyonder422.com

View 4 Replies View Related

Measure Data Transfer Between Replicated Servers

Jul 20, 2005

Hello AllI was wondering if there's a way to monitor/measure data-transferbeing taking place between 2 serves in a replicated environment.I cannot see any counters, etc. to monitor this..??thanksSunit

View 1 Replies View Related

Last Time Publication/subscription Replicated Data

Jun 21, 2007

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??



I want to set up monitoring to make sure I am aware if something for some reason does not replicate



OH and it is on SQL 2005 enterprise... an

View 2 Replies View Related

Sql Server 2005 Consolidation

Dec 19, 2007

Can anyone point me in the direction of some best practice resources for consolidating Sql 2005 on a 64 but Unisys platform?

The web seems a bit sparse unfortunately and most of the best practice information seems oriented around 2000. This is a great article but Im not sure how much has changed in 2005 http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx .

View 3 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

Replication :: Data Belonging To Specific Warehouse Replicated To Its Corresponding DB

Jul 10, 2015

We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS.  There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?

View 2 Replies View Related

Data Driven Site

Jan 19, 2007

I have a question has anyone seen a Webbased Database made with Frontpage 2003 before???

View 2 Replies View Related

Data Driven Web Site

Dec 29, 2006

I am using vb .net 2003 with msde.

I have downloaded microsoft`s vbsdk setup and have it working pretty well until I get to the login page and the provided credentials will not work.

The sample tells me to set permissions in the dacl editor.

1:What is the dacl editor and how do I use it?

2:What do I set permissions to?

I have been through the code provided over and over and can`t seem to find anything wrong,So I figure maybe this is the problem.

Any help will be appreciated.

Davco

View 1 Replies View Related

Import Data From A Web Site

Dec 20, 2006

I'm trying to use 2005 Integration Services to import data from a web address into a SQLServer 2005 database.

The address I want to download data from is http://www.nymerc.com/futures/innf.txt

I'm not sure how I am supposed to access the data on the website. What kind of connection manager do I use? Flat File? HTTP? When I try to use a flat file connection manager, I set the connection string to 'http://www.nymerc.com/futures/innf.txt', but when I click OK, the connection string gets changed to 'c:documents and settings....Temporary Internet FilesContent.IE5V01H744)innf.txt'

Is this expected?

What's the best practice for using a web page as a data source?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved