Monday, March 26, 2012
Need help
We have publisher and remote distributor with lot of transactional and
merge publications. (SQL2K SP3)
Recently we migrated our publisher to a new machine.
Today I renamed my old publisher server and tried to disable subscribers on
it (because everything works on a new publisher already).
When I did that via EM, all my replication agents on a distributor failed
(probably the old publisher wrote somethig to the distributor).
Then I marked (enabled) all subscribers back on that server (old publisher).
After that all merge replications became good again. Buh the transactional
ones don't work.
I get an error "The remote procedure call failed and did not execute"
Need help.
Thanks
Hi,
How did you rename the old publihser from EM? I'm not familiar with that capability.
-Matt
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Need Feedback on Trans. Replication w/ Remote Distributor
I have been asked to set up replication between two SQL servers on our
network. Though I am primarily a network security engineer, and would
consider myself just above a novice SQL Admin, replication is definitely new
territory for me, and I was hoping I could get some help and feedback on
what I think I am trying to accomplish...
The scenario: We have a SQL server that currently serves as the backend
database for a web-based application. It consists of several databases (on
one instance) with no clustering nor data redudancy other than the RAID
structures currently housing the data. A second server has been purchased
with much more processing power, that they wish to use as a failover device,
but do not wish to make it the PRODUCTION box until/unless the current box
fails. They just want a copy of the data to be duplicated to another device
as close to real-time as possible (and without clustering).
So, because of the power of the new box compared to the old one, I had
decided based on my initial research, to set up Transactional Replication
between the two boxes, and defining the new server (Server B) as the Remote
Distributor and Subscriber with the original production server (Server A) as
only a Publisher. I also intended/hoped to use pull subscriptions. The
intent of all these decisions being to absolutely minimize the additional
overhead on the original production box. What I have not been able to find
is any documentation on how to configure an alternate snapshot location when
using a remote distributor that is the sole subscriber. Can this be done?
The reason I need to use alternate snapshots (I believe) is because when I
do implement this on the production servers, they exist in a DMZ zone that
not only has no domain, but also does not have any NetBIOS nor windows mgmt
protocols enabled (except Terminal Services for Remote Admin). There is NO
Windows SMB file sharing, so I need snapshots to be distributed to the
subscriber via FTP ... but as I said, the subscriber IS the distributor.
Can this be done? Did I inadvertently make my first replication project too
complex? Did I overlook something as to how it can be done? Basically I am
stuck at the point where I have definied my first susbscription, did NOT
create an initial snapshot yet, and am trying to figure out how to configure
the 'Snapshot Location' properties on the Publisher so that it will deploy
the snapshot to a location that the Distributor/Subscriber can access via
FTP, BEFORE running the snapshot agent for the first time.
And yes, this is all on a test environment using Virtual PC's at the moment.
My apologies for the length of this message, but I know how much it helps to
have as much detail up front as possible. And Thank you in advance for any
feedback or recommendations.
Keith C. Jakobs, MCP
[FYI... Exec mgmt has INSISTED that all System files, program files, SQL
data and transaction logs are configured on the SAME RAID-5 partition on 4
physical disks with a 5th hot spare drive... I've tried to convince them to
allocate dedicated log drives and pull swap files off the RAID-5, but they
are not interested in deploying multiple physical disk structures on a
single server]
First, don't apologize for the post length. I strongly prefer a detailed
post with relevant information to a "my server don't work, help, TIA" post.
Second, Transactional replication will give you a read-only copy of the
data, but not of the complete database schema. All objects don't get
replicated and stuff like Identity columns, referential integrity, unique
constraints, views, and stored procedures just won't work the way you think
it will. The short answer is that without a LOT of extra work, you can't
use a subscriber in place of a publisher as a DR strategy.
If you don't use any file copy protocols other than FTP, how do you get
backups off of the host computer? There is such a thing as too much
security.
I would use Log Shipping to handle the creating and maintaining a warm
standby server. There are some scripts in the SQL 2000 Resource Kit as well
as various web sites that you can adapt for your own use.
I realize this isn't answering the question you asked, but it may be the
answer you really need.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
> Greetings:
> I have been asked to set up replication between two SQL servers on our
> network. Though I am primarily a network security engineer, and would
> consider myself just above a novice SQL Admin, replication is definitely
> new
> territory for me, and I was hoping I could get some help and feedback on
> what I think I am trying to accomplish...
> The scenario: We have a SQL server that currently serves as the backend
> database for a web-based application. It consists of several databases
> (on
> one instance) with no clustering nor data redudancy other than the RAID
> structures currently housing the data. A second server has been purchased
> with much more processing power, that they wish to use as a failover
> device,
> but do not wish to make it the PRODUCTION box until/unless the current box
> fails. They just want a copy of the data to be duplicated to another
> device
> as close to real-time as possible (and without clustering).
> So, because of the power of the new box compared to the old one, I had
> decided based on my initial research, to set up Transactional Replication
> between the two boxes, and defining the new server (Server B) as the
> Remote
> Distributor and Subscriber with the original production server (Server A)
> as
> only a Publisher. I also intended/hoped to use pull subscriptions. The
> intent of all these decisions being to absolutely minimize the additional
> overhead on the original production box. What I have not been able to
> find
> is any documentation on how to configure an alternate snapshot location
> when
> using a remote distributor that is the sole subscriber. Can this be done?
> The reason I need to use alternate snapshots (I believe) is because when I
> do implement this on the production servers, they exist in a DMZ zone that
> not only has no domain, but also does not have any NetBIOS nor windows
> mgmt
> protocols enabled (except Terminal Services for Remote Admin). There is
> NO
> Windows SMB file sharing, so I need snapshots to be distributed to the
> subscriber via FTP ... but as I said, the subscriber IS the distributor.
> Can this be done? Did I inadvertently make my first replication project
> too
> complex? Did I overlook something as to how it can be done? Basically I
> am
> stuck at the point where I have definied my first susbscription, did NOT
> create an initial snapshot yet, and am trying to figure out how to
> configure
> the 'Snapshot Location' properties on the Publisher so that it will deploy
> the snapshot to a location that the Distributor/Subscriber can access via
> FTP, BEFORE running the snapshot agent for the first time.
> And yes, this is all on a test environment using Virtual PC's at the
> moment.
> My apologies for the length of this message, but I know how much it helps
> to
> have as much detail up front as possible. And Thank you in advance for
> any
> feedback or recommendations.
> Keith C. Jakobs, MCP
>
> [FYI... Exec mgmt has INSISTED that all System files, program files, SQL
> data and transaction logs are configured on the SAME RAID-5 partition on 4
> physical disks with a 5th hot spare drive... I've tried to convince them
> to
> allocate dedicated log drives and pull swap files off the RAID-5, but they
> are not interested in deploying multiple physical disk structures on a
> single server]
>
|||Hi Geoff,
Thank you so much... yes, this is probably exactly the kind of information
I was in need of. I knew I was missing something about all of this
replication business!!!
I will start reading into Log Shipping, hoping the BOL will give me a good
foundation. I have only vaguely heard the term before, so I will need to
get myself up to speed in that arena. I'll save my questions until I have
at least done my own preliminary studying. But I do need to ask if that can
be done over FTP protocols?
As for backups, we use Veritas NetBackup and have uniquely specified ports
for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt has
also decided that all backups go through our 100Mbit firewall... that's
why they want a standby DB in the DMZ. I would rather a dedicated mgmt
network on secondary NICs, but I dont get to make the final say.... go
figure. ;-)
However, nowadays, I'm beginning to wonder if there is such a thing as too
much security... I've been finding the tighter the better - you know...
single function and hard-coded applications are making more and more sense.
Plus they keep passing more and more laws that you may be better off erring
on the side of excess. Just my 2 cents on the security end.
Thanks again Geoff! Your feedback was very much appropriate and
appreciated!!!
Keith C. Jakobs, MCP
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> First, don't apologize for the post length. I strongly prefer a detailed
> post with relevant information to a "my server don't work, help, TIA"
post.
> Second, Transactional replication will give you a read-only copy of the
> data, but not of the complete database schema. All objects don't get
> replicated and stuff like Identity columns, referential integrity, unique
> constraints, views, and stored procedures just won't work the way you
think
> it will. The short answer is that without a LOT of extra work, you can't
> use a subscriber in place of a publisher as a DR strategy.
> If you don't use any file copy protocols other than FTP, how do you get
> backups off of the host computer? There is such a thing as too much
> security.
> I would use Log Shipping to handle the creating and maintaining a warm
> standby server. There are some scripts in the SQL 2000 Resource Kit as
well[vbcol=seagreen]
> as various web sites that you can adapt for your own use.
> I realize this isn't answering the question you asked, but it may be the
> answer you really need.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
> news:OIcAYyM2FHA.4008@.tk2msftngp13.phx.gbl...
purchased[vbcol=seagreen]
box[vbcol=seagreen]
Replication[vbcol=seagreen]
A)[vbcol=seagreen]
additional[vbcol=seagreen]
done?[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
distributor.[vbcol=seagreen]
I[vbcol=seagreen]
deploy[vbcol=seagreen]
via[vbcol=seagreen]
helps[vbcol=seagreen]
4[vbcol=seagreen]
they
>
|||Built-in log shipping works over SMB protocols. I suppose you could build
something over HTTP/FTP protocols for the file transfers, but I wouldn't
want to write it. I do recommend a second, closed SQL management/backup
network but as you said, we don't always get the final say on such
decisions. BOL describes the built-in stuff for Log Shipping in Enterprise
Edition. The SQL 2000 resource kit has some more information on roll your
own log shipping. You can always Google the term and get many results from
various web sites.
I agree that tighter security is a good thing, but arbitrarily saying that
all communications have to go through X is not security, it is
simplification for network managers.
If you are migrating to SQL 2005, there is a new type of replication called
Peer-to-Peer that may meet your needs. Here is teh latest version of
SQL2005 BOL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>
|||Here is the link.
http://www.microsoft.com/downloads/d...displaylang=en
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Keith Jakobs, MCP" <elohir@.NOSPAM.hotmail.com> wrote in message
news:uXZNXRY2FHA.744@.TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
> Thank you so much... yes, this is probably exactly the kind of
> information
> I was in need of. I knew I was missing something about all of this
> replication business!!!
> I will start reading into Log Shipping, hoping the BOL will give me a good
> foundation. I have only vaguely heard the term before, so I will need to
> get myself up to speed in that arena. I'll save my questions until I have
> at least done my own preliminary studying. But I do need to ask if that
> can
> be done over FTP protocols?
> As for backups, we use Veritas NetBackup and have uniquely specified ports
> for that traffic through our firewall between LAN & DMZ. Yes, Exec mgmt
> has
> also decided that all backups go through our 100Mbit firewall...
> that's
> why they want a standby DB in the DMZ. I would rather a dedicated mgmt
> network on secondary NICs, but I dont get to make the final say.... go
> figure. ;-)
> However, nowadays, I'm beginning to wonder if there is such a thing as too
> much security... I've been finding the tighter the better - you know...
> single function and hard-coded applications are making more and more
> sense.
> Plus they keep passing more and more laws that you may be better off
> erring
> on the side of excess. Just my 2 cents on the security end.
> Thanks again Geoff! Your feedback was very much appropriate and
> appreciated!!!
> Keith C. Jakobs, MCP
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:OcVcS$Q2FHA.400@.TK2MSFTNGP09.phx.gbl...
> post.
> think
> well
> purchased
> box
> Replication
> A)
> additional
> done?
> I
> that
> distributor.
> I
> deploy
> via
> helps
> 4
> they
>
need faster data transfer :: oledb
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
There are way to many reasons for it to be slow. It could be your server, their server, network, etc.The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.
When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||
Nitin Khurana wrote:
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
There are so many "it depends" answers to this that its hardly worth replying.
There is a webcast on performance tuning that you may wish to watch:
Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)
And info here about a performance tuning whitepaper:
Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)
-Jamie
|||
Nitin Khurana wrote:
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.
Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||
Nitin Khurana wrote:
okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..
Are there indexes in place on the destination tables?
Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?
Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?
Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?
=================
The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)
Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)
Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.
Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)
*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||
Nitin Khurana wrote:
okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..
Only you can answer those questions. Test and measure is my advice to you.
There is some useful performance tuning info in the whitepaper that I linked to earlier.
You might also want to look at using the SQL Server Destination if at all possible.
-Jamie
|||
<<after filtering ,i.e which need to be transfered>>
This is where I would look first. That and network lag.
|||
I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.
I have no idea why it would take so much longer.
Sam
|||samssb wrote:
I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.
I have no idea why it would take so much longer.
Sam
Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.
need faster data transfer :: oledb
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
There are way to many reasons for it to be slow. It could be your server, their server, network, etc.The best performance you will get would be a "Execute SQL Task" and not a Data Task. You might need to do a "SELECT * INTO temptable FROM REMOTE.table" and then do your transforms into the real target table on your server.
When I have to do this kind of work, I usually just transfer the entire target to a new database and then do my work on my server.|||
Nitin Khurana wrote:
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
There are so many "it depends" answers to this that its hardly worth replying.
There is a webcast on performance tuning that you may wish to watch:
Donald Farmer's Technet webcast
(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/4076.aspx)
And info here about a performance tuning whitepaper:
Whitepaper on Performance Tuning Techniques
(http://blogs.conchango.com/jamiethomson/archive/2006/04/09/3594.aspx)
-Jamie
|||
Nitin Khurana wrote:
i need to fetch data into multiple tables from a remote server (ss 2005)...so inside a sequence container , i have placed various data flow task...each task in turn gets data from oledb source to oledb destination.....there is minimal transformation involved...but the data is huge...and its taking unexpectably long...(im gettin only fresh data..identified by datetime fields of source tables..thats the only check.),
and way to make it faster...source db in not in my control...so i cant get indexes on it..:(
Be sure you are using a SQL select statement in your OLE DB Source. Select just the columns you need, and if you need them all, "select *" has shown to be faster than simply selecting the table from the drop-down box.
Are your data flow tasks hooked sequentially to each other, or are you running as many as you can in parallel? If you're running them in parallel, try completing one first before moving on to the next one.|||okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..|||
Nitin Khurana wrote:
okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..
Are there indexes in place on the destination tables?
Try this:
Copy your package and in the new one (package_copy), take your OLE DB source and go right into a row counter and only a row counter -- nothing else. Run the package. How long does it take?
Then create another copy of your "master" package and in that copy (package_copy2) replace your destination with a row counter. How long does that take?
Create a copy of your "package_copy" package and remove the row counter and replace it with a raw file destination. Run the package. Then, delete everything and start over with a raw file source (pointing to the file just created) and then attach it to a row counter. How long does this new package take to run?
=================
The first test tells you how long it takes to get data from the source as fast as you can. (VALUEA)
The second test tells you how long it takes to get through the data flow, not counting the destination. (VALUEB)
The third test tells you how efficient the source pull is. (VALUEC)
Take the total package run time (your problem) and subtract from it VALUEB. This number tells you how long it takes to put data into the destination table. (VALUED)
Take VALUED and subtract from it VALUEA. This tells you how long it takes to transform the data.
Take VALUEA and subtract from it VALUEC. This tells you how inefficient the source pull is if the difference is not zero. (not likely)
*** NOTE: I first learned of this technique by watching a Webcast by Donald Farmer titled, SQL Server 2005 Integration Services: Performance and Scale.|||
Nitin Khurana wrote:
okkk...let me tell a bit more.... network/server config etc are not my scope of prob...there r other packages performing gud...to be specific, im using the fast load option, (oledb source and destination) , in few tables , number of rows are over a few million (after filtering ,i.e which need to be transfered), these few tables are the bottleneck ..consider both source and destination as highend servers...what are the options i can set for rows per batch,..commit size,etc , size of rows is not much either , say 200 - 300 KB.... so what are the options i have..
Only you can answer those questions. Test and measure is my advice to you.
There is some useful performance tuning info in the whitepaper that I linked to earlier.
You might also want to look at using the SQL Server Destination if at all possible.
-Jamie
|||
<<after filtering ,i.e which need to be transfered>>
This is where I would look first. That and network lag.
|||
I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.
I have no idea why it would take so much longer.
Sam
|||samssb wrote:
I am having the same problem. Taking data from 1 table and copying it to another table. The table only consists of 3 columns, 1 is a data type image. In SSMS it also takes forever to open the table so I can see part of the reason why this package is taking so long. But it is taking about 20 minutes to get the data from the source and another 20 minutes to commit the data. With the old dts package it used a copy sql server objects task and the whole job ran in about 20 minutes. So I also tried using a transfer sql server objects task to copy the data from this one table and it is also taking around 40 minutes.
I have no idea why it would take so much longer.
Sam
Did you run the test that I indicated above? That will help determine if it's the source pull or the destination push that is causing the bottleneck. However, ensure that you are using FAST LOAD in the OLE DB destination, otherwise you'll get slow performance.sql
Friday, March 23, 2012
Need Enterprise Manager for Remote Site
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
Thanks
Paul Bergson
If that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:
> I need to get Enterprise Manager to a user at a remote site and I don't want
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mmc
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>
|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/d...displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql
Need Enterprise Manager for Remote Site
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
Thanks
Paul BergsonIf that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:
> I need to get Enterprise Manager to a user at a remote site and I don't wa
nt
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mm
c
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/...&displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com
Need Enterprise Manager for Remote Site
to provide shared access to my sql 2000 install cd.
What/where can I download to provide a user with the Enterprise Manager mmc
console snap-in?
--
Thanks
Paul BergsonIf that is a "licenced" user, you can get him a copy of the cd, but if he is
not licenced he is not allowed to install it. There is no snap in
downloadable for the enterprise manager, it all comes with the cd.
Another options would be: http://www.aspfaq.com/show.asp?id=2442
HTH, Jens Suessmeyer.
"Paul Bergson" wrote:
> I need to get Enterprise Manager to a user at a remote site and I don't want
> to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise Manager mmc
> console snap-in?
>
> --
> Thanks
> Paul Bergson
>
>|||Paul Bergson wrote:
> I need to get Enterprise Manager to a user at a remote site and I
> don't want to provide shared access to my sql 2000 install cd.
> What/where can I download to provide a user with the Enterprise
> Manager mmc console snap-in?
Try using the SQL Server Web Data Administrator instead:
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&displaylang=en
David Gugick
Quest Software
www.imceda.com
www.quest.com
need direction on SSB replacing existing technology
Currently, we get the data back and forth by creating text files full of SQL statements that are run on either the end user servers or the main server, and we zip and ftp these SQL Script files back and forth.
I am looking into the benefits of replacing this process with a Service Broker application, but I'm still too much of a newbie to understand how this might work. What should my messages contain? Should they contain the same SQL statements that are currently in the script files, and then a SSB process would grab the SQL and run it? Or should the message types be more specific to each application?
any pointers to articles that go beyond "here's what SSB is" would be appreciated.
matt tagliaferri
Why aren't you considering SQL Server Replication?|||I've used replication for other tasks - I've found it to be a bit difficult to manage and troubleshoot. Ansynchonous messaging seems to most closely match our existing method of sending the data around (scripts via ftp).
matt tag
|||
Implementing replication on top of Service Broker will require a lot of effort in terms of building the plumbing to read/write data, marshalling/unmarshalling, managing schemas, monitoring, configuration, etc. I'm not sure if it actually helps reduce the problem.
Service Broker is useful when you truely want to build loosely-coupled apps. That is apps that are not bound w.r.t. availability, time, schemas, administrative control, identities, scalability, etc. But it is more general purpose than replication and therefore requires more effort.
Wednesday, March 7, 2012
need a remote bcp or similar utility
Kevin,
for bcp.exe it doesn't matter whehter the data files or database servers are
remote or local. So, you should be able to load your data with no problem.
Tengiz Khartishvili
SQL Server Storage Engine
This posting is provided "as is" with no warranties and confers no rights.
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:9D3090CE-91A8-42B1-8C40-928FC0DC33EC@.microsoft.com...
> We are looking for a way to load a database with bcp during an install.
The catch is that the input file and the install (which would kick off the
bcp load) can be on a different machine than the database (and the bcp.exe).
Any ideas?
Saturday, February 25, 2012
Need a connection string for sql server via vb6
sorry to bother you with something so newbish, but I am trying to connect to a remote sql server db, using vb6.
I want to use ado to do so but have been having problem with some of the samples I have found online.
I will probably just test the connection out on an existing database like pubs or northwind.
Can anyone provide me a code snip please?
This forum has always been a great resource for me, thanks again guys!Driver={SQL Server};Server=localhost;Address=localhost,1433;Ne twork=DBMSSOCN;Database=northwind;Uid=sa;Pwd=;
Switch the appropriate data out
Rob|||You also might want to add www.connectionstrings.com to your favorites.