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 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 Processing Extensions and Data Sources

Anyone know about DPE? Specifically, I want to know if it can help me to
make a data source dynamic in a multi-user environment.
I need to integrate SRS into my web app, which allows the user to select a
database from a list before running a report. Several people could try to
run the same report at the same time. If I use the API
"SetDataReportSources" then this switches the data source for all users of
the report. As noted before in this group, this is a limitation of SQL RS.
What I really want to know is: Can I develop a DPE that allows me to
dynamically set the data source at run-time? This is a requirement for my
application, and I want to avoid creating multiple copies of reports or
passing the data source in a stored procedure. Either of these work-arounds
involve more maintenance and hassle for the future. Can DPE allow me to make
a permanent solution?
I looked in the books online, and read about implementing a connection
class. If I made my own connection object, could I set it dynamically at run
time?
Hopefully,
MalikI'm trying to do the same thing as well but we havn't had the time or
resource yet to finish it off. Once I have a DPE running I would be happy to
share it on gotdotnet. Or if you get there first maybe you could to.
Try this link for further info. This is where I got started. I have aslo
uploaded some VB code to Gavin's page to complement the C# he has already.
It's still not as straight forward as you might hope but it will acomplish
what you are after.
http://weblogs.asp.net/gavinjoyce/archive/2004/01/29/64339.aspx?Pending=true
Regards
Toby,.
"Abdul Malik Said" <diplacusis@.hotmNOSPAMail.com> wrote in message
news:O4sJVKDZEHA.556@.tk2msftngp13.phx.gbl...
> Anyone know about DPE? Specifically, I want to know if it can help me to
> make a data source dynamic in a multi-user environment.
> I need to integrate SRS into my web app, which allows the user to select a
> database from a list before running a report. Several people could try to
> run the same report at the same time. If I use the API
> "SetDataReportSources" then this switches the data source for all users of
> the report. As noted before in this group, this is a limitation of SQL RS.
> What I really want to know is: Can I develop a DPE that allows me to
> dynamically set the data source at run-time? This is a requirement for my
> application, and I want to avoid creating multiple copies of reports or
> passing the data source in a stored procedure. Either of these
work-arounds
> involve more maintenance and hassle for the future. Can DPE allow me to
make
> a permanent solution?
> I looked in the books online, and read about implementing a connection
> class. If I made my own connection object, could I set it dynamically at
run
> time?
> Hopefully,
> Malik
>

Data mining licensing requirements

I am developing a data mining solution for a client using SQL 2005.

Do they have to purchase a separate license for a replicated environment? Like a hot site....

Thanks, ]

BI Joe

Data Mining is part of SQL Server and has the same licensing requirements. E.g. if it's on the web, than you need the per processor license, etc.

I'm not an expert on all of the licensing issues, but, in short, there's no difference between using the relational engine, reporting services, analysis services, etc - the licensing requirements are the same.

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
Phil
Inline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixtur
e
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocat
e
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.

> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.

> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.

> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.

> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA

>

Data handling in a production environment

Hi,
Please bear with me, this is a fairly long and general post. Please answer
any of these questions or point me to appropriate references...thanx...
My company manufactures some product and currently have some EOL testers
which write test results for serialised parts into text files using a mixture
of VB and standard programs like testpoint and labview. These files may be
accessed or written to by several testers in parallel or at different points
in the production process. This occurs in three seperate manufacturing
locations that are networked on a WAN.
We are considering updating some of these EOL test programs to write the
test results into a database. The benefits that we forsee in doing this are
that we could improve the speed of read / write transactions (would this
improve write speed?), improve ease of reporting / data gathering, and could
backup the data more easily.
We have SQL server 2000 standard(2 processor) on one site which would be the
final repository for all the data, and each site would generate a maximum of
100,000 records / day. Bearing this in mind I am looking at the
network/database design and have questions regarding:-
1. Database to use on each site and where to locate it?
Should each EOL tester have it's own database or should there be one
dedicated database server on each site?
MSDE2000 or MySQL, as far as features, ease of use, and most of all
manageability, which is better, and what sort of maintenance tasks would we
need to perform on each?Could these tasks be easily automated?
2. Stored Procedures? I have been looking at stored in SQL server. Is
this the best was of managing the inserting of EOL test data into the
database? Could this confirm that the insert worked? could it also allocate
a new serial number? What other ways are there to do this? Are there any
examples you can point me to?
3. Additional metrics to gather:- are you aware of any standard prouction
reporting/analysis tools I could look at? We would make reports available
through an intranet.
Thank you in advance for your help
PhilInline responses...
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:95EA188E-7C59-495C-BA75-E971E2BA3FA6@.microsoft.com...
> Hi,
> We are considering updating some of these EOL test programs to write the
> test results into a database. The benefits that we forsee in doing this
are
> that we could improve the speed of read / write transactions (would this
> improve write speed?), improve ease of reporting / data gathering, and
could
> backup the data more easily.
Yes these are all huge benefits. Writing transactions may not improve the
speed, but would greatly improve the reliability. Transactions follow the
ACID format and are better.
> 1. Database to use on each site and where to locate it?
> Should each EOL tester have it's own database or should there be one
> dedicated database server on each site?
> MSDE2000 or MySQL, as far as features, ease of use, and most of all
> manageability, which is better, and what sort of maintenance tasks would
we
> need to perform on each?Could these tasks be easily automated?
One dedicated database server on each site, unless you have the bandwidth
(and network uptime) to use the single SQL Server on the dual processor
machine. It should easily handle 300k transactions in a day. (You can
optimize this system with multiple NICs as well.).
If you do a dedicated at each site, MSDE is great for small databases. If
you are going to upload all of the data each day and then clear the
database, this should not pose any problems and is an inexpensive way of
doing things.
> 2. Stored Procedures? I have been looking at stored in SQL server. Is
> this the best was of managing the inserting of EOL test data into the
> database? Could this confirm that the insert worked? could it also
allocate
> a new serial number? What other ways are there to do this? Are there any
> examples you can point me to?
>
Stored procs are definitely the way to go here. They help to reduce network
traffic and can take multiple inputs and generate multiple outputs. They
can allocate new serial numbers and a variety of other things. You could
use a trigger to generate a new serial number, but the stored procedure
would be the recommended method. You could skip the stored procedures and
use a front-end program to generate the INSERT statements complete with the
necessary data and serial numbers etc.
I would still recommend the stored procedures.
> 3. Additional metrics to gather:- are you aware of any standard
prouction
> reporting/analysis tools I could look at? We would make reports available
> through an intranet.
SQL Reporting Services is free with SQL Server. You could start there.
The other big dog in the Microsoft world is Crystal Reports which are
capable of generating a report in a variety of formats including HTML, XML
and PDF. You could always write an ASP or ASP.NET (or J2EE) application
that can do the number crunching and generate the ASP(x) pages.
> Thank you in advance for your help
> Phil
No problem, I'll send you my bill later. <wink>
Rick Sawtell
MCT, MCSD, MCDBA
>