data synchronization accross multiple servers running replication

Hello,

We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.

In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?

Thanks,

Phil

In SQL Server 2005 there is a tablediff tool which you can use to compare the tables. You can also use the validation options built in replication to verify if the data are in sync.

Here are the validation options:

In transactional replication: sp_publication_validation

In merge replication: use the -validate parameter for merge agent

|||

Li,

Thanks for the reply. I believe we can use this. Do you know how well it does as far as speed is concerned? I think that's our main concern right now. Also, I'm reading a bit on the uitlity and I'm wondering how does it take a "snapshot" of a table on multiple servers and compare that. Also you wouldn't happen to have any syntax examples would you?

Thanks,

Phil

|||

Also, does anyone know if there is a way to get the utility to use windows authentication instead of coding the sa password in the utility statement?

Thanks,

Phil

data synchronisation

Hi,

I'm working with two databases on to physicaly separated servers: #1
is on a local machine en I can only connect to #2 via an ADSL
connection.
I need to syncrhonise the data. This works fine via the wizard from
the Enterprise Manager. But I want to get this done via a script form
the Query Analyzer that I can run whenever I want to. Something like:

insert into srv1.database.dbo.table1
select * from srv2.database.dbo.table1

When I do this I get an error about "linked servers". That surprises
me, because I have no linked servers configured and yet the wizard
works fine?

Can anyone tell me how to accomplish this?

tav
Jan Willemsjhmwillems@.hotmail.com (Jan) wrote in message news:<240176c1.0404060206.4bfaaca3@.posting.google.com>...
> Hi,
> I'm working with two databases on to physicaly separated servers: #1
> is on a local machine en I can only connect to #2 via an ADSL
> connection.
> I need to syncrhonise the data. This works fine via the wizard from
> the Enterprise Manager. But I want to get this done via a script form
> the Query Analyzer that I can run whenever I want to. Something like:
> insert into srv1.database.dbo.table1
> select * from srv2.database.dbo.table1
> When I do this I get an error about "linked servers". That surprises
> me, because I have no linked servers configured and yet the wizard
> works fine?
> Can anyone tell me how to accomplish this?
> tav
> Jan Willems

The Import/Export Data wizard uses a separate connection to each
server, so linked servers are not involved. The INSERT statement above
does require the linked servers to exist, however.

So you could just create the linked servers you need, or if that's not
possible for some reason, then you can save a DTS package from the
wizard, and execute it in whatever way is most convenient:

http://www.sqldts.com/default.aspx?104

Simon

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file (create a new text document, then rename it to "test.udl"). Open the UDL file by double-clicking it, then try to establish a connection to your database using the same sorts of parameters (ie provider [SQL Server native / ODBC etc.], server name, login parameters). You can use this to narrow down the cause of the problem (eg. whether it's the DB provider, or a lack of connectivity which can be checked using other means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP? Also, it'd depend on whether you've got the IIS machine on the Publisher machine, the Subscriber machine, or another machine?
Also try playing around with the configuration of the Client/Server Network Protocol - if you're having problems using TCP/IP, then try using Named Pipes instead. Make sure to configure this on both the publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

sql

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data Retrieval

Our production environment has multiple SQL Servers installed and running and
i need to retrieve data from multiple different servers and databases. We
have established multiple Web Service DAL's to retrieve data for out
applications but i cannot see how to leverage that into Reporting Services.
Any ideas?
Thanks in advance
Goaler...Read up on data processing extentions. That should fullfill your needs.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> Our production environment has multiple SQL Servers installed and running
and
> i need to retrieve data from multiple different servers and databases. We
> have established multiple Web Service DAL's to retrieve data for out
> applications but i cannot see how to leverage that into Reporting
Services.
> Any ideas?
> Thanks in advance
> Goaler...|||Thaks Bruce
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||As a follow-up, I think i need a bit more clarification and understanding.
Here is the issue
I need to be able to read multiple predefined shared data sources from
within MS-Reporting Services, to avoid the configuration of connection
information out side of the product. Then to combine the result set and
return it into a new report object.
There must be a way to do this, the designer allows you to create multiple
data sources but you cannot join the data together. Additionally, linked
servers are not an option in my environment.
Thanks again,
"Bruce L-C [MVP]" wrote:
> Read up on data processing extentions. That should fullfill your needs.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "goaler" <goaler@.discussions.microsoft.com> wrote in message
> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
> > Our production environment has multiple SQL Servers installed and running
> and
> > i need to retrieve data from multiple different servers and databases. We
> > have established multiple Web Service DAL's to retrieve data for out
> > applications but i cannot see how to leverage that into Reporting
> Services.
> >
> > Any ideas?
> >
> > Thanks in advance
> > Goaler...
>
>|||You can not join datasets together.
Your clarification confused me, I thought you had web services that you
wanted to use to return the data? If so then data processing extensions are
the way to go.
Bruce Loehle-Conger
"goaler" <goaler@.discussions.microsoft.com> wrote in message
news:6C959BD5-C681-4E15-96EE-78EA69C282BA@.microsoft.com...
> As a follow-up, I think i need a bit more clarification and understanding.
> Here is the issue
> I need to be able to read multiple predefined shared data sources from
> within MS-Reporting Services, to avoid the configuration of connection
> information out side of the product. Then to combine the result set and
> return it into a new report object.
> There must be a way to do this, the designer allows you to create multiple
> data sources but you cannot join the data together. Additionally, linked
> servers are not an option in my environment.
> Thanks again,
> "Bruce L-C [MVP]" wrote:
>> Read up on data processing extentions. That should fullfill your needs.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "goaler" <goaler@.discussions.microsoft.com> wrote in message
>> news:92060CC9-0812-4BCB-A4D8-E69FF51C58D2@.microsoft.com...
>> > Our production environment has multiple SQL Servers installed and
>> > running
>> and
>> > i need to retrieve data from multiple different servers and databases.
>> > We
>> > have established multiple Web Service DAL's to retrieve data for out
>> > applications but i cannot see how to leverage that into Reporting
>> Services.
>> >
>> > Any ideas?
>> >
>> > Thanks in advance
>> > Goaler...
>>

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison
sql

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison

data reader source in data flow problem

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.

-Jamie

|||

hi jamie,

thank you for your advice but i still did not understand completely.

the example u sent me is with ole db data source. lets say i have created the variable

and put there the select. now how do i config the data flow to use it?

can you give me another example?

thx,

Tomer

|||

Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.

-Jamie

|||

hi again Jamie,

i have put there:

"select * from x where y=" + @.[user::max_date]

and i get an error.

can u advice?

|||

Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.

-Jamie

|||

Jamie,

thank you very much for your help - you realy helped me here and saved me lots of time.

tomer

|||

Hi Jamie,

yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?

why can u advice me on that please?

tomer

|||

You have made a change in one of your components that means it won't work anymore. It should tell you which component.

-Jamie

|||

it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:

"select * from x where y = " + [user::x]

when x is a string type. i must have changed somthing small without noticing...?

|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?

Data Provider List Missing...very odd SQL 2000

Hey All!

I have an interseting situation with one of my dev machines. (W2k3EE, SQL2k SP4)

All my linked servers stopped working posting this error:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider [enter provider name here, e.g. 'SQLOLEDB'.

So...when attempting to drop and recreate the linked server, the Provider pull down in the GUI is completely empty.

YIKES!

I'm thinking of reinstalling MDAC, but I've never seen this in 10 years of DBA'ing.

Have any of you? Any thoughts out there? Registry issues?

Cheers,
OddNever seen anything like it either. I'm running the same combination in my environment, so if you have any solutions, please post.

Some thoughts and/or suggestions:

1. Was the server recently patched?

2. Was any new software (eg. driver package) recently installed?

3. Any entries in the Application or System error log(s)?

4. Do you back up the registry on this machine? Can it be restored?

Regards,

hmscott|||Thanks for the reply...will definitely post what I find...

1 - yeah, SP4
2 - prolly so...the network guys love to drop agents on my dev servers without mentioning it...Alteris monitor, i think this time
3 - nada
4 - heck no, that would be too easy

:)

Data Mining in analyzing servers

I am trying to setup a means to analyze our servers using SQL Server 2005 Data Mining. I will be collecting all the logs from the Event Log of each server and store them in a SQL Server database for analysis.

Has anybody done this before? Can you give some pointers? I would appreciate your assistance.

I've thought about this problem a bit, but not had the time to implement any solution. Generally my thoughts are around defining a "failure" variable, and then using log information to predict that failure, or to predict time to failure.

"Failure" in this case could be anything, for example

Users can't connect

Data Migration using Transactional Replication

I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.

I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.

Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.

Note: Migration is from SQL Server 2000 to SQL Server 2005.

Thanks in advance.

Riteshi do not think replication is going to be easy and effective solution for 200 DB with 2.5 TB. replication has its own set of rules, issues and overheads. better move a few db at a time (backup/restore or detach/attach) and then point the application using those databases to the new db server.

Data Lost-Merge Replication

We are using merge replication over internet to repliacte our data b/w 2 servers located at 2 different branch offices. But the strange thing is we are losing data i.e. data is being automatically deleted during replication.

any idea/help for fixing it would be hihgly appreciated

thank in advanceProbably failed insert at subscriber poss due to Referential Integrity Failure.

Because SQL Must make publisher & Subscriber with same data it will come back & delete publisher row

Keep an eye on the conflicts

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKJon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||What exact steps did you take? How did you "build a new log file"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB servers failed.
> The DB server was given separate physical disks to split the data and txn log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pretty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 11:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Had a problem recently with some lost data after a disk in one of our DB
>> servers failed.
>> The DB server was given separate physical disks to split the data and txn
>> log files on different disks to guard against disk failure.
>> The disk with the txn log failed at 14:30. We got the DB online again
>> pretty quickly by building a new log file for the DB. However, the last
>> data in the DB was written at 11:45. I'm not expecting to recover the
>> data now but was confused to see the lost data.
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
>> Is there anything I should be checking on the server ?
>> TIA,
>> Jon A
>> London, UK
>>
>

data loss when reinitializing subscription

we had setup merge replication on 2 db servers. For some reason, the subscription started failing a month back with the error " invalid object sysmergexxxx on the subscriber. I did a reinitialize and now all the changes on the subscriber which werent synced got deleted. I have tried all 3 log recovery tools with no luck. Is there any hope of recovering data. the last backup on the subscriber was a month ago.

I believe when you reinitialized the subscriber, you did not chose to upload the unsent changes from subscriber and hence you lost your subscriber data. Sorry to say, but there is no way to recover this data now and is overwritten by the publisher's data.

You should try to either upload the changes next time this occurs or save a backup copy of the subscriber database, then reinitialize (without upload changes) and then diff between the current subscriber and the backup and redo the DML that are different.

Hope that helps next time.

I am also moving this thread to the replication forums.

Data Flow Task question

Hi there. I'm trying to learn SSIS, please, help me. I have 2 questions:

1)
There are 2 databases on 2 different servers. I need to get data from Table1(database1) and put it to Table2(database2). But I have to insert rows, which ID is not exists in Table2. How Can I do necessary filter?

2)
In the OLE DB DataSource Component I have used SQL Command(it's simplified):

declare @.TmpTable TABLE (WorkCode int not null);

INSERT INTO @.TmpTable (WorkCode)
select WorkCode
from Table1

SELECT WorkCode
FROM @.TmpTable

SSIS Package works without any exception. But there is no any inserted record in destination table. If I try similar query without temporary table - it works good. Why?

1 - Create OLE DB source to 1st (source) database.
- Add a lookup transformation to select key from 2nd database on 2nd server. In that lookup join the key coming from the 1st database to the key in the 2nd
- Hook the error output (red arrow) from the lookup to an OLE DB destination which points to the 2nd database. This will insert records not found in the 2nd database.

2 - Try setting the RetainSameConnection property of the connection manager to true and see what happens.|||The first task works! Thanks! But the second is not. Any other ideas?
|||

Aliaksander Hmyrak wrote:

The first task works! Thanks! But the second is not. Any other ideas?

Good deal.

As for number 2, why are you using temp tables? 2 things - you can just write the query that inserts into the temp table as the source for the data flow. OR you could use yet another, initial, data flow to populate a SQL server table with the results you need in the 2nd data flow (the one you've already got written). After the two data flows, you could write an Execute SQL task in the control flow to truncate that "temporary" table.|||

At the start of your SQL statement add

Code Snippet

SET NOCOUNT ON

|||Thanks a lot !! Both methods work!