Outbound SQL Connections

Hello Everyone,

I have two SQL 2005 clusters, one at a Corporate HQ and another at a hosting site. On each cluster there are several SQL instances each tied to a seperate Cluster Group meaning that there is a virtual cluster IP address assigned to the cluster group which should translate to the instance.

The issue that I am seeing is that in the connection manager, the IP addresses aren't listed correctly and there is sometimes just junk in there -- up to 10 IP addresses listed. There are many replication and service broker jobs running between the clusters all tied to specific IP addresses and ports which map to NAT and ACLs on the firewalls.

Here is the crux of it: While I can change the listening port and IP in the connection manager, how to I ensure that the outbound replication traffic is coming from the right source? If I add the correct IP address into the connection manager for the instance and disable the others, will it send from that IP address?

I have inherited this system and we are making the first strides into intagling this mess, but I am trying to surgically fix this rather than opening up all kinds of security holes in the interim.

Suggestions?

Ryan

Moving to Availability folder.

Outbound SQL Connections

Hello Everyone,

I have two SQL 2005 clusters, one at a Corporate HQ and another at a hosting site. On each cluster there are several SQL instances each tied to a seperate Cluster Group meaning that there is a virtual cluster IP address assigned to the cluster group which should translate to the instance.

The issue that I am seeing is that in the connection manager, the IP addresses aren't listed correctly and there is sometimes just junk in there -- up to 10 IP addresses listed. There are many replication and service broker jobs running between the clusters all tied to specific IP addresses and ports which map to NAT and ACLs on the firewalls.

Here is the crux of it: While I can change the listening port and IP in the connection manager, how to I ensure that the outbound replication traffic is coming from the right source? If I add the correct IP address into the connection manager for the instance and disable the others, will it send from that IP address?

I have inherited this system and we are making the first strides into intagling this mess, but I am trying to surgically fix this rather than opening up all kinds of security holes in the interim.

Suggestions?

Ryan

Moving to Availability folder.

Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab

Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I would first analyze the sql server for bottlenecks. Most of the problems
can be attributed to bad design, poor i/o design , indexing. Have all these
been checked?
Jim
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:%235$vpBwyHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>

Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab

Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?Can you define "maxed out"? And what are the actual symptoms?
--
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:
>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT

Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab

Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Please provide additional details about your SQL cluster (hardware, SQL
server version, configuration, etc.) and what constitutes "maxed out". Where
is the bottleneck -- memory, disk, cpu, network? Performance monitor can
help narrow this down.
Also, moving tables off of one cluster to another may make sense under
certain circumstances but it requires knowing the data and how it is accessed
in the very least to be able to give any informative advice. SQL Server 2005
provides some nice functionality along this line.
"Daniel" wrote:

> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that just
> create processing bottleneck on the cluster running SQL server?
>
>

Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab

Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
I'll have to agree. When the speakers sit around in the lounge at a big
conference we trade war stories about clients that have had literally tons
of hardware made unnecessary by some judicious tuning of the database or the
queries. In one case the customer (a BIG company) had not bothered to add
indexes to the tables. They were able to eliminate over half of their
servers. They saved a bunch of money on electricity and the salary of the
kid that designed the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"JPD" <nospam@.nospamplease.com> wrote in message
news:Ovf3gapzHHA.3536@.TK2MSFTNGP06.phx.gbl...[vbcol=seagreen]
> Hi Daniel,
> What do you mean by maxed out. Is it "maxed out" on I/O, memory, CPU? Run
> a trace in Performance Monitor using the following counters and once
> you've identified the bottlenecks act accordingly, i.e. add more memory or
> more/faster disks or RAID, fix application problems (if you're getting
> blocking situations), remove other apps on the server etc. etc. But before
> you take action you need inform yourself on what the problem really is.
>
> a. LogicalDisk (All Counters and All Instances)
> b. Memory (All Counters)
> c. Paging File (All Counters and All Instances)
> d. PhysicalDisk (All Counters and All Instances)
> e. Processor (All Counters and All Instances)
> f. SQLServer:Access Methods (All Counters)
> g. SQLServer:Buffer Manager (All Counters)
> h. SQLServer:Buffer Node (All Counters and All Instances)
> i. SQLServer:Buffer Partition (All Counters and All Instances)
> j. SQLServer:Databases (All Counters and All Instances)
> k. SQLServer:General Statistics (All Counters)
> l. SQLServer:General Latches (All Counters)
> m. SQLServer:General Locks (All Counters and All Instances)
> n. SQLServer:General Memory Manager (All Counters)
> o. SQLServer:General Plan Cache (All Counters and All Instances)
> p. SQLServer:General Transactions (All Counters)
> q. SQLServer:General Wait Statistics (All Counters and All Instances)
> r. Thread (All Counters and All Instances)
>
> Jonathan
>
> Daniel wrote:

Our high end SQL server cluster is maxed out, how else to expand? Will it help to move tab

Our high end SQL server cluster is maxed out, how else to expand? Will it
help to move tables off of the cluster onto other clusters or will that just
create processing bottleneck on the cluster running SQL server?
Can you define "maxed out"? And what are the actual symptoms?
Aaron Bertrand
SQL Server MVP
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eHiAoBwyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> Our high end SQL server cluster is maxed out, how else to expand? Will it
> help to move tables off of the cluster onto other clusters or will that
> just create processing bottleneck on the cluster running SQL server?
>
|||On Fri, 20 Jul 2007 12:01:39 -0700, "Daniel"
<softwareengineer98037@.yahoo.com> wrote:

>Our high end SQL server cluster is maxed out, how else to expand? Will it
>help to move tables off of the cluster onto other clusters or will that just
>create processing bottleneck on the cluster running SQL server?
Please don't post the same question to multiple groups. If you must
send one to two groups, but BOTH groups on the SAME copy so that
replies are seen in both places. (It helps if you use the Subject for
a short Subject too.)
I'm no cluster expert, but when the experts get here they will have
some questions. You might want to have answers ready.
What is maxing out? Disk space? CPU utilization? Memory? The
network?
What does high-end mean in this instance? How many servers, how many
and what type processors, how muchy memory, what sort of disk system?
What is the structure of the cluster? How many machines, running what
operating system/version, running what SQL Server
version(s)/instances? What is the failover arrangement?
Generally the smallest unit of SQL Server that can be moved without
re-writing something is a database. Tables fall within databases. A
question about moving tables sounds like you are a system
administrator but not a DBA. With a high-end cluster that is maxing
out you need the hands-on attention of a DBA.
Roy Harvey
Beacon Falls, CT