anyone use a 3rd party product to load balance SQL Server?

I'm working on a project needing a high-end large mission critical database
solution. The data is in 1 database. So SQL 2000 doesn't support load
balancing, and neither will 2005. That sucks. Oracle supposedly does load
balancing and I can't understand why even SQL Server 2005 won't do it. So
either I go with an 8+ CPU SQL Server or consider a 3rd party load balancer
that will somehow (?) make it work so that multiple SQL brains can balance
the load of 1 single database that is presumably shared on 1 set of disks.
I'm finding very little information on load balancing SQL Server when
searching the net. Is there a product like Legato, for example, (I haven't
tried it) that will somehow give me load balancing with SQL Server?HK wrote:
> I'm working on a project needing a high-end large mission critical
> database solution. The data is in 1 database. So SQL 2000 doesn't
> support load balancing, and neither will 2005. That sucks. Oracle
> supposedly does load balancing and I can't understand why even SQL
> Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> consider a 3rd party load balancer that will somehow (?) make it work
> so that multiple SQL brains can balance the load of 1 single database
> that is presumably shared on 1 set of disks.
> I'm finding very little information on load balancing SQL Server when
> searching the net. Is there a product like Legato, for example, (I
> haven't tried it) that will somehow give me load balancing with SQL
> Server?
You may want to take a look at Federated Database Servers on SQL Server
2000/2005 if that architecture will work for you.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uzLN8u5wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> HK wrote:
> > I'm working on a project needing a high-end large mission critical
> > database solution. The data is in 1 database. So SQL 2000 doesn't
> > support load balancing, and neither will 2005. That sucks. Oracle
> > supposedly does load balancing and I can't understand why even SQL
> > Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> > consider a 3rd party load balancer that will somehow (?) make it work
> > so that multiple SQL brains can balance the load of 1 single database
> > that is presumably shared on 1 set of disks.
> >
> > I'm finding very little information on load balancing SQL Server when
> > searching the net. Is there a product like Legato, for example, (I
> > haven't tried it) that will somehow give me load balancing with SQL
> > Server?
> You may want to take a look at Federated Database Servers on SQL Server
> 2000/2005 if that architecture will work for you.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Creating the partitioned views sounds very challenging for an already-in-use
DB that needs to be cut over in the middle of the night. Plus ongoing
maintenance. Maybe I'm missing the boat, but that's why I'd want to stay
away from federated databases.

Anyone know table limits in multi-schema environment?

My product is growing rapidly and currently I have a db for each client with identical schema. Of course maintenance is pretty hard. I was thinking of using a shared db but having a schema for each client (sql 2005) - I have almost 100 tables in the schema which means with just 10 clients the db would pass 1000 tables. My gut is telling me this ain't going to fly!

any ideas? and if it does work ... any thoughts on updating the internal schemas for each client?

thanks

-c

You definately can get over 1000 tables, since certain complex ERP systems and such have that just on their own, and that was even in older version of SQL Server.

According to :http://msdn2.microsoft.com/en-us/library/aa933149(SQL.80).aspx

The amount of tables is actualy only limited by the amount of objects (which means all triggers, tables, stored procedures, etc count toward this limit). The limit is... 2,147,483,647

Good luck busting that. Oh, and thats SQL Server 2000, was too lazy to find the 2005 specs :)|||

Sounds responable to me. I was going off this article from MS that suggested no more than 100 tables per client schema in a single db but they don't specify why :)

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

Any idea how to do updates to the schema? my current thinking is to get my app to login as each schema owner and execute the update script.

thanks

-c

Anyone heard of product for sqls called MyDTS?

Who makes this? It's some sort of dts writer/scheduler I think.there is dts that comes with sqlserver. mydts sounds like someone's dts package.

http://groups.google.com/groups?q=mydts

Anyone else finding this painful?

Although I am sure I will grow to love this new product I can honestly say that everyday has been a painful experience. Most of this, once again, is the learning curve to the new product. Moreover, each accomplishment leaves us feeling as if we are doing things the wrong way.

Here is an example... We are populating lookup tables from a staging environment. This is pretty straight forward, insert records that don't already exist in the destination. Unfortunately, the only way we could get this to work properly, outside of just writing T-SQL for everything, was to use a lookup transformation and redirect the error rows into the table. Although we have ran numerous tests against it with no problems it certainly doesn't let you walk away with a very comfortable feeling and certainly isn't a choice I would recommend. Are others having this same struggle and uncomfortableness?

Select aspirin
into Body
from Ailments
where condition like '%SSIS%'

-Krusty

Krusty,

Don't fret. That's a legitimate approach and ndeed is the commonly accepted approach. Fair enough - it doesn't look right that good rows are coming down the Error output but don't worry - its not a problem.

-Jamie

|||I feel your pain; but Jamie is right. This is a pretty common technique. I was not aware of the Slowly Changing Dimension Wizard which does a variation of the same thing with a twist that I haven't figured out yet. The wizard can be found in the data flow task by the same name and might be worth looking at sometime - for now, we do it exactly the way you are doing it. I did not realize I had a Slowly Changing Dimension... so I wrote this data flow on my own. The Wizard does it for you.|||

Note that the SCD uses a LOOKUP under the covers so its basically the same thing with a few bells and whistles.

-Jamie

|||

Thanks for the comments it is somewhat comforting to know that others are using the same approach. I guess after being comfortable in the 2000-DTS world for so long it is tough to walk into an environment that puts you back at the beginning.

-Krusty

PS: Thanks for all the blogging Jamie... between here and there I have managed to make some progress.

|||

Hi,

Do not you find that the Slowly Changing Dimension transform works a little too slow?

Sure it saves you tons of code but I would love it to be faster.

Philippe

|||Hey Krusty - I tend to agree with you, there should be an easier and cleaner way to accomplish your task. The techniques described above do work, but it feels like a hack. There should be a transform or something that allows you to "insert where not exists". This is fairly common scenario in my experience.|||

An Upsert estination adapter would be fantastic. Insert it if its not there, update it otherwise.

-Jamie

|||

There would need to be more metatdata entered and persisted in the package to support that type of functionality (the unique identifiers that determine whether a row is a "new" row or exists) so that SSIS could "know" which operation to perform. Solende's Warehouse Workbench has exactly this functionality in the GUI. I loved it.

Of course also it should examine the actual data in each column of source vs. target if the decision is to update ,as none of the data may actually have changed. In that case you would probably not want to perform an update on the target for no real reason that may also update a timestamp column or some other indicator in many systems that indicates when rows have been updated. Many systems that have such a column also have logic to make sure an update is performed only when data actually is changed.

Ken

|||I think it is a very common scenario. My current SSIS project is

pulling updated/new rows from Oracle and transferring to staging

tables. I played with the Lookup transforms, but balked at writing

basically the same DataFlow stuff many times over for many different

tables.

In the end I just wrote minimal DataFlows for the transfer to staging,

then produced update/insert tsql stored in files and used a ForEach

loop to iterate over them. It means as I implement new table transfers,

I can just drop new script files into a particular folder (location

configurable with variable) and know they will be executed in the

loop. I took the idea from the AdventureWorks DataWarehouse SSIS

example.

- Jerzy|||That is a very interesting idea... I will have to take a look at that option. Thanks.

Antivirus on a 2003 Cluster

Hi all
I am trying to work out if it is ok to install an antivirus product onto my
Windows 2003 SQL Cluster.
According to Symantec I can:
http://service1.symantec.com/SUPPORT... er=savce_9.0
but Microsoft don't seem too keen on the idea:
http://support.microsoft.com/default...;EN-US;Q250355
Has anyone managed to install antivirus onto a cluster with no problems or
is it best just to leave my servers as they are?
Thanks
Kristi
You can install, just exclude the MSCS directory and where you put your SQL
Data.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
"Krissi" <krissi@.hotmail.com> wrote in message
news:42636498$1@.quokka.wn.com.au...
> Hi all
> I am trying to work out if it is ok to install an antivirus product onto
> my
> Windows 2003 SQL Cluster.
> According to Symantec I can:
> http://service1.symantec.com/SUPPORT... er=savce_9.0
> but Microsoft don't seem too keen on the idea:
> http://support.microsoft.com/default...;EN-US;Q250355
> Has anyone managed to install antivirus onto a cluster with no problems or
> is it best just to leave my servers as they are?
> Thanks
> Kristi
>
|||Trend Micro with Server Protect have a good solution.
"Krissi" wrote:

> Hi all
> I am trying to work out if it is ok to install an antivirus product onto my
> Windows 2003 SQL Cluster.
> According to Symantec I can:
> http://service1.symantec.com/SUPPORT... er=savce_9.0
> but Microsoft don't seem too keen on the idea:
> http://support.microsoft.com/default...;EN-US;Q250355
> Has anyone managed to install antivirus onto a cluster with no problems or
> is it best just to leave my servers as they are?
> Thanks
> Kristi
>
>
|||Agreed, that is what I recommend and use, but you still need to exclude
directories
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
"LUCA MOTTERLE [MCP]" <LUCAMOTTERLEMCP@.discussions.microsoft.com> wrote in
message news:E2242F94-D213-430C-85C4-DF8712B455D3@.microsoft.com...[vbcol=seagreen]
> Trend Micro with Server Protect have a good solution.
> "Krissi" wrote: