Network Considerations For Mirroring

I would like to know people's thoughts on any special network considerations to take for mirroring and the logic behind them. Is it best to segregate mirroring traffic from other network traffic? Use a VLAN? Dedicate one NIC for mirroring and the other for general network traffic or just aggregate the two and let both types of traffic share the bandwidth?

I haven't seen much in this area from Microsoft's best practices and wanted to know what those who have implemented it have done and why. There are pros and cons for each method: Letting everything share one massive pipe with load balancing vs. trying to segregate traffic in some way so that general network connections etc. do not impact the mirroring capability.

I look forward to hearing from you.

-M-

Hi M

Answer to your question depends on the following:

1. how much cash you got.

2. How busy your databases are going to be.

3. How busy and fast your network is?

Database mirroring can be treated like clustering where servers are next to each other and connected on dedicated connection. Believe me I have done this

On the other hand I have used mirroring where servers in two different data centers (safety OFF).

Quite difficult for an outsider to give any definite answers without proper analysis.

Best thing to do is to configure mirroring and run stuff like re-indexing and may be do some bulk inserts and also do some testing by users and see if the performance is acceptable.

regards

Jag

|||

If you havent already got information you require..

Read this , the article discusses about DBM setup under various network topologies

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

Hi,

I have a typical situation as mentioned below:

Please let me know how to handle and let me know what to use: Clustering or Database Mirroring.

Clustering software is installed on N nodes. The node that is used for client access is controlled by a quorum. There are two quorum implementation strategies.
"Windows Server 2003 has two types of cluster quorum models: the traditional shared quorum failover cluster, which uses a shared disk to hold the cluster state, and the local quorum or Majority Node Set (MNS) cluster, which uses a local disk on each node to hold the cluster state. SQL Server 2005 supports both cluster quorum models."
Our application would simply need to be configured to point to one virtual IP address provided by the cluster.

Please help me for setting up this.

Thanks

Network Considerations For Mirroring

I would like to know people's thoughts on any special network considerations to take for mirroring and the logic behind them. Is it best to segregate mirroring traffic from other network traffic? Use a VLAN? Dedicate one NIC for mirroring and the other for general network traffic or just aggregate the two and let both types of traffic share the bandwidth?

I haven't seen much in this area from Microsoft's best practices and wanted to know what those who have implemented it have done and why. There are pros and cons for each method: Letting everything share one massive pipe with load balancing vs. trying to segregate traffic in some way so that general network connections etc. do not impact the mirroring capability.

I look forward to hearing from you.

-M-

Hi M

Answer to your question depends on the following:

1. how much cash you got.

2. How busy your databases are going to be.

3. How busy and fast your network is?

Database mirroring can be treated like clustering where servers are next to each other and connected on dedicated connection. Believe me I have done this

On the other hand I have used mirroring where servers in two different data centers (safety OFF).

Quite difficult for an outsider to give any definite answers without proper analysis.

Best thing to do is to configure mirroring and run stuff like re-indexing and may be do some bulk inserts and also do some testing by users and see if the performance is acceptable.

regards

Jag

|||

If you havent already got information you require..

Read this , the article discusses about DBM setup under various network topologies

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

Hi,

I have a typical situation as mentioned below:

Please let me know how to handle and let me know what to use: Clustering or Database Mirroring.

Clustering software is installed on N nodes. The node that is used for client access is controlled by a quorum. There are two quorum implementation strategies.
"Windows Server 2003 has two types of cluster quorum models: the traditional shared quorum failover cluster, which uses a shared disk to hold the cluster state, and the local quorum or Majority Node Set (MNS) cluster, which uses a local disk on each node to hold the cluster state. SQL Server 2005 supports both cluster quorum models."
Our application would simply need to be configured to point to one virtual IP address provided by the cluster.

Please help me for setting up this.

Thanks

Network Considerations For Mirroring

I would like to know people's thoughts on any special network considerations to take for mirroring and the logic behind them. Is it best to segregate mirroring traffic from other network traffic? Use a VLAN? Dedicate one NIC for mirroring and the other for general network traffic or just aggregate the two and let both types of traffic share the bandwidth?

I haven't seen much in this area from Microsoft's best practices and wanted to know what those who have implemented it have done and why. There are pros and cons for each method: Letting everything share one massive pipe with load balancing vs. trying to segregate traffic in some way so that general network connections etc. do not impact the mirroring capability.

I look forward to hearing from you.

-M-

Hi M

Answer to your question depends on the following:

1. how much cash you got.

2. How busy your databases are going to be.

3. How busy and fast your network is?

Database mirroring can be treated like clustering where servers are next to each other and connected on dedicated connection. Believe me I have done this

On the other hand I have used mirroring where servers in two different data centers (safety OFF).

Quite difficult for an outsider to give any definite answers without proper analysis.

Best thing to do is to configure mirroring and run stuff like re-indexing and may be do some bulk inserts and also do some testing by users and see if the performance is acceptable.

regards

Jag

|||

If you havent already got information you require..

Read this , the article discusses about DBM setup under various network topologies

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

|||

Hi,

I have a typical situation as mentioned below:

Please let me know how to handle and let me know what to use: Clustering or Database Mirroring.

Clustering software is installed on N nodes. The node that is used for client access is controlled by a quorum. There are two quorum implementation strategies.
"Windows Server 2003 has two types of cluster quorum models: the traditional shared quorum failover cluster, which uses a shared disk to hold the cluster state, and the local quorum or Majority Node Set (MNS) cluster, which uses a local disk on each node to hold the cluster state. SQL Server 2005 supports both cluster quorum models."
Our application would simply need to be configured to point to one virtual IP address provided by the cluster.

Please help me for setting up this.

Thanks

sql

Nested SQL with condition logic

I'm trying to combine two posts I found into one solution but I think I'm going for the impossible.

Here's a shell of the nested select and I've indicated where I'd insert the nested logic.

SELECT node_id, fk_id, nd_src_tbl_name, ( ** conditional code ** )
FROM vNodeCollection as vNc

Here's the nested logic I want to insert for each row:

BEGIN
IF [vNc].nd_src_tbl_name = 'tbl_CorporateRegion'
BEGIN
SELECT cr_textFROM tbl_CorporateRegion WHERE cr_id = [vNc].fk_id
END
ELSE IF [vNc].nd_src_tbl_name = 'tblCountry'
BEGIN
SELECT country_text FROM tbl_Country WHERE country_id = [vNc].fk_id
END
END

I'm trying to be dynamic by storing my table names with my node detail information but I'm starting to realize this may not work well.

Any ideas?No, this will not work. If u want conditional statements in a select, use the CASE functionality. See Books Online for details on how to use it. Alternatively, a user defined function may work as well (if u have SQL Server 2000).|||I forgot about custom functions. After some stumbling with the syntax I was able to make it work. I have no idea if it's efficent though.

Thanks for the idea.

Nested SQL or IF END LOGIC ?

Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.

episode_key chart_component_key deficiency_type deficiency_status status_date 13789881 173398 408 C 8/4/2007 13789881 173488 409 S 8/4/2007 13789881 173703 409 S 8/6/2007 13789881 174568 1028 S 8/7/2007 13789881 176213 421 S 8/9/2007 13789881 176214 421 S 8/9/2007 13789881 176215 421 S 8/9/2007 13789881 176216 421 S 8/9/2007 13789881 176218 421 S 8/9/2007 13789881 176219 406 D 8/9/2007

Can someone help me here?

Chuck,

You problem and sample data are a bit ambiguous.

It appears that all of this data belongs to one Episode -since there is but one Episode_key value.

Every Chart_Component-key appears to be unique.

So what is the expected result from this set of data?

|||

This should do it for you

Code Snippet

DELETED - read edit

Edit : I re-read the question and realized you said "all deficiency_status is equal to 'C'" not just that one of the records in the episdoe is set to 'C'.

|||

This one makes sure that ALL the definciency code fields in the recordset of one episode are 'C'

Code Snippet

CREATE Table episode_table (

episode_key INT,

chart_component_key INT,

deficiency_type INT,

deficiency_status CHAR,

status_date DATETIME

)

INSERT INTO episode_table VALUES (13789881, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789881, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789881, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789881, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789881, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789881, 176219, 406, 'D', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789882, 173488, 409, 'S', '8/4/2007' )

INSERT INTO episode_table VALUES (13789882, 173703, 409, 'S', '8/6/2007' )

INSERT INTO episode_table VALUES (13789882, 174568, 1028, 'S', '8/7/2007' )

INSERT INTO episode_table VALUES (13789882, 176213, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176214, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176215, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176216, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176218, 421, 'S', '8/9/2007' )

INSERT INTO episode_table VALUES (13789882, 176219, 406, 'D', '8/12/2007' )

INSERT INTO episode_table VALUES (13789883, 173398, 408, 'C' ,'8/4/2007')

INSERT INTO episode_table VALUES (13789883, 173488, 409, 'C', '8/4/2007' )

INSERT INTO episode_table VALUES (13789883, 176219, 406, 'C', '8/12/2007' )

SELECT et1.episode_key, MAX(et1.status_date)

FROM episode_table et1 LEFT OUTER JOIN episode_table et2 ON

-- You only have to include the unique primary key fields here, i didn't want to assume

-- that you had one though

(et1.episode_key = et2.episode_key AND et1.chart_component_key = et2.chart_component_key AND

et1.deficiency_type = et2.deficiency_type AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C')

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)

I joined the tables on all fields to be sure, but you only need to include the fields in the primary key if there is one, and the deficiency status clause.|||Each patient episode may involve several physicians involved in their case and making notes in the chart. Each chart_component_key should signify a different physician making notes in the chart. A "completed" chart is defined as when all physicians have completed their charting thus reflecting a "C" in the deficiency_status fields.

I need to return a "Completed Chart Date" which would mean that all chart_component_keys would be "C" and I would then take the MAX(status_date) as the completed chart date.

|||

Thanks Shawn. I was able to modify this and make it work. This piece of code was to pull the final field in a report I am working on. I am trying to plug it into my main SQL code for the big report, but having difficulties. I'll start a new thread for this issue.

Thanks!

|||

You can use the query below:

Code Snippet

select t.episode_key, max(t.status_date) as completed_chart_date
from <your_table> as t
group by t.episode_key
having count(*) = sum(case t.deficiency_status when 'C' then 1 end);

|||

Very nice Umachandar!

I have to remember that approach, very efficient.