Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
sql7perftune.asp" target="_blank">http://msdn.microsoft.com/library/d...ql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
GriffGriff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>|||> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

Bulk insert - to log or not to log

Dear all
I have a web application which has many customers. Each customer has their
own system, which means they have their own database. Each customer has
many users.
Out of office hours, I allow the customer to run very large data updates
against their systems. These are bulk inserts which use minimal logging. I
do this to ensure that they take as little time as possible.
However, some customers demand that they can do "small" data updates during
the day. If the data updates fall below a particular size threshold, then
they run during office hours. If one runs, there's minimal performance loss
to the other users on the server. However, if more than one runs at the
same time, then the server basically grinds to a halt, giving interactive
users extremely poor performance.
It was recommended to me that during office hours I should do bulk inserts
that were fully logged instead of minimally logged. The reasoning was that
the logging operation takes time, ensuring that the bulk insert can't hog
the processor, giving the interactive user far better performance. The
upload may take up to 10 times longer though.
This makes sense to me, but unfortunately I can't test it without doing it
on the live server. Any thoughts on this?
Thanks in advance
Griff
Griff
>It was recommended to me that during office hours I >should do bulk inserts
>that were fully logged instead of minimally logged.
It depends as far as I know on recovery model of the database. Only if you
have full recovery model the bulk operation is fully logged. if the
transaction is failed you are able to restore the log file at point of time.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>
|||
> It depends as far as I know on recovery model of the database. Only if you
> have full recovery model the bulk operation is fully logged. if the
> transaction is failed you are able to restore the log file at point of
time.
Hi Uri
True, but presumably the recovery option can be changed so that it's FULL
during office hours and BULK-LOGGED at other times. I'm not sure though
whether it's sensible to keep changing the recovery model...my guess is
not.
Griff
|||Full recovery model will certainly cause a big increase in IO activity in
your log file during th day. Writing those log records also cnosume a lot of
CPU. It is doubtful that other database users can see better performance
while this is going on. I can not say for sure that you will see a
performance degradation, but the chance is pretty high.
I would start with checking the following things first:
Do you have blocking and deadlock problem when you introduce more than one
update workload (bulk insert)?
Is there lock escalation?
Is it 100% CPU? -- check performance monitor
Or Is it very long disk queue length? -- check performance monitor
A starting point is to read this article:
http://msdn.microsoft.com/library/de...l7perftune.asp
and you will find some really great SQL Server performance tuning articles
on the web.
I recommend that you have a test system to experiment with various settings.
If you are MSDN subscriber, you may also want to check out SQL Server 2005
beta 2. It has a feature called "read committed snapshot" that may increase
throughput if you have a blocking and deadlock problem.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griff" <Howling@.The.Moon> wrote in message
news:uVuQX7$jEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Dear all
> I have a web application which has many customers. Each customer has
their
> own system, which means they have their own database. Each customer has
> many users.
> Out of office hours, I allow the customer to run very large data updates
> against their systems. These are bulk inserts which use minimal logging.
I
> do this to ensure that they take as little time as possible.
> However, some customers demand that they can do "small" data updates
during
> the day. If the data updates fall below a particular size threshold, then
> they run during office hours. If one runs, there's minimal performance
loss
> to the other users on the server. However, if more than one runs at the
> same time, then the server basically grinds to a halt, giving interactive
> users extremely poor performance.
> It was recommended to me that during office hours I should do bulk inserts
> that were fully logged instead of minimally logged. The reasoning was
that
> the logging operation takes time, ensuring that the bulk insert can't hog
> the processor, giving the interactive user far better performance. The
> upload may take up to 10 times longer though.
> This makes sense to me, but unfortunately I can't test it without doing it
> on the live server. Any thoughts on this?
> Thanks in advance
> Griff
>

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I jus
t
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

BULK INSERT

Question: All of my developers (2000) use DTS to load data. However, I just
had the web admin come to me and want to set up a user that would be able to
be BULK ADMIN and load using BULK INSERT statements.
I'm a little afraid of that because BULK INSERT can read anywhere on the
drive(s), right? So someone could figure out a way to look at most files on
the drive(s), right?
Actually, I can't think of any files (other than the mdf's) that have
sensitive data. So is this really a big security concern?
Any insight would be much appreciated...
Hi
You may want to do this of a batch job and just do it at a scheduled
(off-peak!) time. Then all the user would have to do is upload the file and
they don't even have to know where it sent!!!
John
"CLM" <CLM@.discussions.microsoft.com> wrote in message
news:A41C37B0-4A99-47BD-A4E2-4E01C3B38D61@.microsoft.com...
> Question: All of my developers (2000) use DTS to load data. However, I
> just
> had the web admin come to me and want to set up a user that would be able
> to
> be BULK ADMIN and load using BULK INSERT statements.
> I'm a little afraid of that because BULK INSERT can read anywhere on the
> drive(s), right? So someone could figure out a way to look at most files
> on
> the drive(s), right?
> Actually, I can't think of any files (other than the mdf's) that have
> sensitive data. So is this really a big security concern?
> Any insight would be much appreciated...

Bulk import images to binary object in SQL 2000

I have a web application that I am rebuilding. I have many picture files that want to take off the file system and move into SQL as a blob. I will create an index of uids against the file names but need a good way to bulk add the files to the database... any hints on code or tools would be a great help.

Thanks

Bill

You can use a utility 'textcopy' in 'Command Prompt' under the SQL instance directory:

C:\Program Files\Microsoft SQL Server\MSSQL$InstanceName\Binn

FYI, you can use it as following, make sure the command in a single line:

====Copy data from file to table====

textcopy /S labsh96223\iori2000 /I /F c:\xml\iori.bmp /D northwind /T testImage /C img /U sa /P /W "where idx=2"
====Copy data from table to file====

textcopy /S labsh96223\iori2000 /O /F c:\xml\iori.jpg /D northwind /T testImage /C img /U sa /P /W "where idx=2"

You can also use some programming code (written in VC++,C# etc.) to manipulte the binary data of BLOBs between files and database.

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building schema and tables from XML?

I'm looking for the "proper" way to do this:

I've been given a web site that generates some XML. I need to load the XML from the site (that's trivial, of course) and then populate a database table with the information contained in the XML.

For example, the XML looks something like this:

<OutputData>
<Response>
<Result code="0">Operation Successful</Result>
<Agents>
<Agent code="452">Bill</Agent>
<Agent code="999">Fred</Agent>
</Agents>
<Stats>
<UpSince>3993848</UpSince>
<LastHit>88288</LastHit>
</Stats>
</Response>
</OutputData>

I could make an XSD by hand, but that would be very cumbersome (the XML is actually quite huge - this is just an example of part of it). I could create the database tables and generate the XSD from them, but the tables wouldn't reflect the schema of the XML perfectly, I suspect.

The goal, of course, being the automation of reading the XML into the database.

So the question - given some XML that someone just throws at you, what's the proper way to create an XSD to read it in and eventually get it into tables in SQL 2005?

Many thanks in advance for some adult supervision :-)There are XML Schema inference tools available (such as XSD.exe in VS), that can generate an initial XSD schema for you. The SQLXML Bulkload object also has a very simple relational schema generation capability to generate tables and columns based on an annotated XSD.

What I would like to better understand though is:

1. Do you have an existing relational schema?
2. Do you know the shape of your XML data in such a way that you could write XPath expressions to propagate the values from the instance document?

If the answers to these questions are "yes", I would recommend to look into using the nodes() method to shred the XML into the relational form and expose this through a stored proc.

Best regards
Michael

Building high available and high performance web sites with MS SQL

Hi all. My first time posting to this newsgroup, but I think I am in the
right place. I am in the discovery stage of building a web site that we
anticipate will have a large amount of traffic. Our design platform is
Windows and Microsoft SQL and the goal is to have web sites and database
servers that are not only high available but also scalable that I can put in
a new server as needed to increase performance.
For the web sites, I know that I can use the NLB services of Windows 2003
standard edition. My lack of knowledge, though, is on the MS SQL side. I
have been reading a lot of info on Microsoft SQL server failover, but from
what I have read this tyipcally entails having two MS SQL servers running in
an active/passive mode.
Does Microsoft SQL Server support the ability to have multiple MS SQL
servers using the same database and appearing as one physically database? If
one fails, the other servers pick up the load, and if we need to add a new
server we simply install it into the cluster?
I understand that I will need enterprise edition to accomplish the goals
above, but are these goals even doable?
I know I can setup a bunch of SQL servers all doing transaction
replication, but that makes each server appear as its own database server and
I feel is not the proper solution for this.
Hope my questions make sense and looking forward to the answers.
You are asking about two goals. First let me addres the availability issue.
Microsoft clustering is a failover technology where a SQL instance can move
between host computers in case of hardware or systems failure. The basic
single-instance, two-node cluster (sometimes incorrectly called
Active/Passive) uses only one host at a time. You can add additional nodes
(host computers) and instances, but the instances do not share information
and are not "brick-type" scalable.
Right now, the way to scale SQL is to replace the host computer with a
larger one. With failover clustering, you can do this with minimal downtime
by replacing one node at a time.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:7A300D7D-7E43-49DF-A3A7-9A134C55AA22@.microsoft.com...
> Hi all. My first time posting to this newsgroup, but I think I am in the
> right place. I am in the discovery stage of building a web site that we
> anticipate will have a large amount of traffic. Our design platform is
> Windows and Microsoft SQL and the goal is to have web sites and database
> servers that are not only high available but also scalable that I can put
> in
> a new server as needed to increase performance.
> For the web sites, I know that I can use the NLB services of Windows 2003
> standard edition. My lack of knowledge, though, is on the MS SQL side. I
> have been reading a lot of info on Microsoft SQL server failover, but from
> what I have read this tyipcally entails having two MS SQL servers running
> in
> an active/passive mode.
> Does Microsoft SQL Server support the ability to have multiple MS SQL
> servers using the same database and appearing as one physically database?
> If
> one fails, the other servers pick up the load, and if we need to add a new
> server we simply install it into the cluster?
> I understand that I will need enterprise edition to accomplish the goals
> above, but are these goals even doable?
> I know I can setup a bunch of SQL servers all doing transaction
> replication, but that makes each server appear as its own database server
> and
> I feel is not the proper solution for this.
> Hope my questions make sense and looking forward to the answers.
|||"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:ehpN6$aCHHA.4024@.TK2MSFTNGP04.phx.gbl...
> You are asking about two goals. First let me addres the availability
> issue. Microsoft clustering is a failover technology where a SQL instance
> can move between host computers in case of hardware or systems failure.
> The basic single-instance, two-node cluster (sometimes incorrectly called
> Active/Passive) uses only one host at a time.
But it is active/passive... <G>
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
|||What is the purpose of adding extra nodes past the 2 node cluster if
clustering is just for failover? I read about adding extra nodes when
researching and didnt see why that would be advantageous? In case multiple
servers malfunction at the same time?
Ok, now that I know Clustering wont solve my problem, is there another
method of doing database load balancing? Would have lets say 3 servers, each
acting as a publisher/subscriber and then a 2-node cluster acting as a
distributer work well in this situation? I feel in this solution, i would
have 3 sql servers with the same info and then a high available distributer.
Then I assume I would need a way to make the 3 sql servers appear as one
server somehow so the code only has to reference one sql server. Maybe round
robin dns?
Am i making this too difficult? Is there an easier solution? I cant
imagine I am the only one who needs a solution where you can scale Microsoft
SQL servers effectively.
Thanks in advance.
"Geoff N. Hiten" wrote:

> You are asking about two goals. First let me addres the availability issue.
> Microsoft clustering is a failover technology where a SQL instance can move
> between host computers in case of hardware or systems failure. The basic
> single-instance, two-node cluster (sometimes incorrectly called
> Active/Passive) uses only one host at a time. You can add additional nodes
> (host computers) and instances, but the instances do not share information
> and are not "brick-type" scalable.
> Right now, the way to scale SQL is to replace the host computer with a
> larger one. With failover clustering, you can do this with minimal downtime
> by replacing one node at a time.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
> news:7A300D7D-7E43-49DF-A3A7-9A134C55AA22@.microsoft.com...
>
>
|||Flaxen,
More nodes means you can spread more SQL instances around the nodes.
Can't really speak to the load balancing aspect of your question.
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
> multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
> each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
> distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
> round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
> Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||There are some thoughts on scaleout options for SQL Server here:
http://msdn2.microsoft.com/en-us/library/aa479364.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
> multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
> each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
> distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
> round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
> Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
One reason is cost. If you can live with the assumption that only one node
may fail at a time, you can have three 'active' nodes sharing a common
'passive' node, i.e. 3+1, and save two servers compared to three sets of
two-node clusters. In case two or more nodes fail at the same time,
performance would suffer on a 3+1 cluster. As long as this is made clear to
the business, it is a small(er) probability sceanrio, and it is clearly
spelled out in the SLA, a 3+1 cluster may be attractive to the business, or
whoever writes the check.
Linchi
"Flaxen" wrote:
[vbcol=seagreen]
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers, each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale Microsoft
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
|||There are two technologies that you can use in conjunction with each other,
if desired, but they are TWO separate technologies, each supporting very
different goals.
Failover clustering, a.k.a. server clusters, support redundant hardware that
exposes a single virtual host name. In the case of hardware failure, the
other node picks up the services, and continues to present the same virtual
server to the network. It is a fail-safe, business resumption solution.
Federated servers, on the other hand, is the closest thing you can come to a
NLB solution for database services. A collection of servers host separate
instances of SQL Server, all with a copy of the database schema; however,
the base tables are partitioned vertically, by date, name, or some other
partitioning function, with linked servers to the other systems. Each
database contains copies of common distributed portioned views, that
aggregate the base tables from across the federation.
Now, since each server exposes this same view across the entire federation,
each is able to process data from all systems. A naive solution, would be
to introduce a load balancing algorithm to distribute client connections
equally across this federation. However, to make optimal use of such a
solution, one would need the application to code which server to connect to
based on the data and which partitioned server supported that data locally,
Data Dependent Routing.
Much of the SS2K documentation is still applicable to SS2K5, but I would
search them both as there have been some enhancements and improvements.
Failover Clustering
http://msdn2.microsoft.com/en-gb/library/ms189134.aspx
SQL Server 2000 Failover Clustering
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
Scaling Out SQL Server with Data Dependent Routing
http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx
Federated SQL Server 2000 Servers
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_4fw3.asp
Federated Database Servers
http://msdn2.microsoft.com/en-us/library/ms190381.aspx
Best of luck.
Sincerely,
Anthony Thomas

"Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...
> What is the purpose of adding extra nodes past the 2 node cluster if
> clustering is just for failover? I read about adding extra nodes when
> researching and didnt see why that would be advantageous? In case
multiple
> servers malfunction at the same time?
> Ok, now that I know Clustering wont solve my problem, is there another
> method of doing database load balancing? Would have lets say 3 servers,
each
> acting as a publisher/subscriber and then a 2-node cluster acting as a
> distributer work well in this situation? I feel in this solution, i would
> have 3 sql servers with the same info and then a high available
distributer.
> Then I assume I would need a way to make the 3 sql servers appear as one
> server somehow so the code only has to reference one sql server. Maybe
round
> robin dns?
> Am i making this too difficult? Is there an easier solution? I cant
> imagine I am the only one who needs a solution where you can scale
Microsoft[vbcol=seagreen]
> SQL servers effectively.
> Thanks in advance.
> "Geoff N. Hiten" wrote:
issue.[vbcol=seagreen]
move[vbcol=seagreen]
basic[vbcol=seagreen]
nodes[vbcol=seagreen]
information[vbcol=seagreen]
downtime[vbcol=seagreen]
the[vbcol=seagreen]
we[vbcol=seagreen]
is[vbcol=seagreen]
database[vbcol=seagreen]
put[vbcol=seagreen]
2003[vbcol=seagreen]
side. I[vbcol=seagreen]
from[vbcol=seagreen]
running[vbcol=seagreen]
database?[vbcol=seagreen]
new[vbcol=seagreen]
goals[vbcol=seagreen]
server[vbcol=seagreen]
|||that article has some details on "scalable shared databases",
this is how you set that up:
http://support.microsoft.com/default.aspx?scid=kb;en-us;910378
obviously scalable shared databases dissadvantage is that the "update
frequency" is very low (it is as far from live/realtime as you can imagine)
HTH,
Edwin.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ub$pxqcCHHA.1300@.TK2MSFTNGP03.phx.gbl...
> There are some thoughts on scaleout options for SQL Server here:
> http://msdn2.microsoft.com/en-us/library/aa479364.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Flaxen" <Flaxen@.discussions.microsoft.com> wrote in message
> news:C29A1C86-1103-4983-BDB6-77C2740EDA1C@.microsoft.com...
would[vbcol=seagreen]
basic[vbcol=seagreen]
is[vbcol=seagreen]
side.[vbcol=seagreen]
a
>

Building custom Reporting Services Toolbar

We have an ASP.Net web app that uses RS for reporting. We can't use the RS
toolbar since we will be generating reports using the SOAP API. Does
anybody have any ideas/suggestions/examples on building a custom toolbar for
RS? Any input will be greatly appreciated. TIA.You just need to create a user control that calls the RS SOAP API for the
functionality you need, e.g. Parameters, etc. The AwReporterWeb project
included in my book source code
(http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
demonstrates how you can get a list of reports and parameters. More involved
scenarios are implemented in the AwReporterWin WinForm demo (e.g. obtaining
the parameters values, multi-value parameters, etc.).
As a side note, you may find my AwReportViewer control useful
(http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
handle images, dataset binding (using my custom dataset extension) and more
:-)
Finally, please note that by going SOAP you are losing not only the report
toolbar but also report interactive features (at least in version 1.0 of
RS), as well as simplicity.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> We have an ASP.Net web app that uses RS for reporting. We can't use the
RS
> toolbar since we will be generating reports using the SOAP API. Does
> anybody have any ideas/suggestions/examples on building a custom toolbar
for
> RS? Any input will be greatly appreciated. TIA.
>
>|||One more thing... you can save yourself quite a bit of development time if
you could postpone the toolbar implementation until the RS 2005 controls are
out. They will include a custom toolbar, events, and will support both
connected and disconnected mode. The first public drop of the controls will
be in Yukon Beta 3 which is just a few months away.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> You just need to create a user control that calls the RS SOAP API for the
> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> included in my book source code
> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> demonstrates how you can get a list of reports and parameters. More
involved
> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
obtaining
> the parameters values, multi-value parameters, etc.).
> As a side note, you may find my AwReportViewer control useful
> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
> handle images, dataset binding (using my custom dataset extension) and
more
> :-)
> Finally, please note that by going SOAP you are losing not only the report
> toolbar but also report interactive features (at least in version 1.0 of
> RS), as well as simplicity.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> > We have an ASP.Net web app that uses RS for reporting. We can't use the
> RS
> > toolbar since we will be generating reports using the SOAP API. Does
> > anybody have any ideas/suggestions/examples on building a custom toolbar
> for
> > RS? Any input will be greatly appreciated. TIA.
> >
> >
> >
>|||Thanks Leo. I've been planning on getting your book for some time now, but
haven't gotten around to it... yet. Now I will have to :-)
I would have prefered to use URL access, and custom authentication, but we
need to upgrade to Enterprise Edition to do that, and the cost is a bit too
much. I understand that the reports will lose some of its interactive
features if I use SOAP API.
"Teo Lachev" <teo@.prologika.com> wrote in message
news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> One more thing... you can save yourself quite a bit of development time if
> you could postpone the toolbar implementation until the RS 2005 controls
> are
> out. They will include a custom toolbar, events, and will support both
> connected and disconnected mode. The first public drop of the controls
> will
> be in Yukon Beta 3 which is just a few months away.
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
>> You just need to create a user control that calls the RS SOAP API for the
>> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
>> included in my book source code
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
>> demonstrates how you can get a list of reports and parameters. More
> involved
>> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> obtaining
>> the parameters values, multi-value parameters, etc.).
>> As a side note, you may find my AwReportViewer control useful
>> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45) to
>> handle images, dataset binding (using my custom dataset extension) and
> more
>> :-)
>> Finally, please note that by going SOAP you are losing not only the
>> report
>> toolbar but also report interactive features (at least in version 1.0 of
>> RS), as well as simplicity.
>> --
>> Hope this helps.
>> ---
>> Teo Lachev, MVP [SQL Server], MCSD, MCT
>> Author: "Microsoft Reporting Services in Action"
>> Publisher website: http://www.manning.com/lachev
>> Buy it from Amazon.com: http://shrinkster.com/eq
>> Home page and blog: http://www.prologika.com/
>> ---
>> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
>> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
>> > We have an ASP.Net web app that uses RS for reporting. We can't use
>> > the
>> RS
>> > toolbar since we will be generating reports using the SOAP API. Does
>> > anybody have any ideas/suggestions/examples on building a custom
>> > toolbar
>> for
>> > RS? Any input will be greatly appreciated. TIA.
>> >
>> >
>> >
>>
>|||You don't have to :-)
I guess at the end you would you need to determine if the money you will
save by using Standard justifies the development effort getting around SOAP
addressability limitations. On the upside, it looks like the 2005 controls
will support drilldown with SOAP.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:ej40s59yEHA.3548@.TK2MSFTNGP09.phx.gbl...
> Thanks Leo. I've been planning on getting your book for some time now,
but
> haven't gotten around to it... yet. Now I will have to :-)
> I would have prefered to use URL access, and custom authentication, but we
> need to upgrade to Enterprise Edition to do that, and the cost is a bit
too
> much. I understand that the reports will lose some of its interactive
> features if I use SOAP API.
> "Teo Lachev" <teo@.prologika.com> wrote in message
> news:uSDp6g4yEHA.1196@.TK2MSFTNGP15.phx.gbl...
> > One more thing... you can save yourself quite a bit of development time
if
> > you could postpone the toolbar implementation until the RS 2005 controls
> > are
> > out. They will include a custom toolbar, events, and will support both
> > connected and disconnected mode. The first public drop of the controls
> > will
> > be in Yukon Beta 3 which is just a few months away.
> >
> > "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> > news:OJs1PZ4yEHA.2196@.TK2MSFTNGP14.phx.gbl...
> >> You just need to create a user control that calls the RS SOAP API for
the
> >> functionality you need, e.g. Parameters, etc. The AwReporterWeb project
> >> included in my book source code
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10394&tstart=45)
> >> demonstrates how you can get a list of reports and parameters. More
> > involved
> >> scenarios are implemented in the AwReporterWin WinForm demo (e.g.
> > obtaining
> >> the parameters values, multi-value parameters, etc.).
> >>
> >> As a side note, you may find my AwReportViewer control useful
> >> (http://www.manning-sandbox.com/thread.jspa?threadID=10392&tstart=45)
to
> >> handle images, dataset binding (using my custom dataset extension) and
> > more
> >> :-)
> >>
> >> Finally, please note that by going SOAP you are losing not only the
> >> report
> >> toolbar but also report interactive features (at least in version 1.0
of
> >> RS), as well as simplicity.
> >>
> >> --
> >> Hope this helps.
> >>
> >> ---
> >> Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> Author: "Microsoft Reporting Services in Action"
> >> Publisher website: http://www.manning.com/lachev
> >> Buy it from Amazon.com: http://shrinkster.com/eq
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >>
> >> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> >> news:OX8WEX0yEHA.2600@.TK2MSFTNGP09.phx.gbl...
> >> > We have an ASP.Net web app that uses RS for reporting. We can't use
> >> > the
> >> RS
> >> > toolbar since we will be generating reports using the SOAP API. Does
> >> > anybody have any ideas/suggestions/examples on building a custom
> >> > toolbar
> >> for
> >> > RS? Any input will be greatly appreciated. TIA.
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>

Build a Web Interface to transfer DATA between Sql servers 2005

Hello,
I would like to know if there is any SDK i can use to build a WebPage
which would enable me to transfer data between sql servers.
Purpose: Non technical people who are not familiar with using
administrative managers, would like to have a publish button where by
they can publish/move data from staging to production. Usually its done
via DTS, but they will not be able to handle DTS.
thanksIs linked servers an option? You didn't specify a language for your
project, but if it's ASP.NET what about just opening up two SqlConnections
to the two different servers and coding SELECTs from one and INSERTs/UPDATEs
into the other? There are a lot of options, but without more specifics it's
hard to say what would be useful for you...
"tillu" <tillu77@.gmail.com> wrote in message
news:1147818011.964849.297580@.j55g2000cwa.googlegroups.com...
> Hello,
> I would like to know if there is any SDK i can use to build a WebPage
> which would enable me to transfer data between sql servers.
> Purpose: Non technical people who are not familiar with using
> administrative managers, would like to have a publish button where by
> they can publish/move data from staging to production. Usually its done
> via DTS, but they will not be able to handle DTS.
> thanks
>

Build a report snapshot from SSIS

Hello,

On my current project we would like to create Report snapshots from SSIS.

I'm fairly sure we can do this by calling the RS web service using the SSIS Web Service Task - but I don't know what method I should be calling nor what the URL may be for the service. Can anyone help?

Thanks

Jamie

If you are creating history snapshots, you want the soap method CreateReportHistorySnapshot. For execution snapshots, use UpdateReportExecutionSnapshot. Documentation is available on these methods at http://msdn2.microsoft.com/en-us/library/ms155076(en-us,SQL.90).aspx