Best scenario for SQL Server 7.0 replication in my situation?

Hi All,
What is the best scenario for seting-up database replication in my
situation?
I have two computers, each computer has...
-W2K, IIS5.0 Web server
-Cold Fusion 4.5 Web Application server
-SQL Server 7.0 database server
-Multihomed IP Addresses using Network Load Balancing
...If one computer goes down for any reason, Network Load Balancing
ensures that the other computer gets all the traffic (Network Load
Balancing is also supposed to split-up traffic between the two
computers, although I have not been able to create this behavior - all
the requests within a session seem to always go to computer #2, unless
it is switched-off, only then will the requests go to computer #1). The
"traffic" is Web requests to our Web site over HTTP and HTTPS.
I want to ensure that each database will "instantly" (or as close to
instantly as possible) take over if the other computer goes down. The
database synchronization needs to be concurrent with minimal latency.
For example, we are linked into Paypal's backend for accepting credit
card payments so we don't want a user to be able to "withdraw money
twice" because of a transaction record not being updated to the other
database.
What are some possible ways of acheiving this?
Thank You,
Nate
I would use a cluster to achieve this, as replication never works in both
directions with 'near to zero' latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the response. Do you mean clustering as in Windows
clustering in "Add/Remove Windows Components" or do you mean some other
clustering that I can setup through SQL Server 7.0?
Thanks Again,
Nate
|||Nate - this is exactly it. There are documents on the MS website and
sqlservercentral explaining how to set it up, but it's not for the
fainthearted, and depending on your background you might need a networking
guy to help get it set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best Practice for Counts/Distinct Count Measures for Filtered Data

Let's say the Count/Distinct Count measure is based on filtered data.

When the situation allows, is it better to do a CASE statement with 1s and 0s from the table in the DSV and then do a SUM on this field? Or is it better to create a new Named Query in the DSV filtering out the data through the WHERE clause based off of the original table and then do a DISTINCT COUNT measure on the newly created Named Query?

If there's no clear cut answer as to what is the "better" approach, please offer up the advantages and disadvantages for both. My preference is best performance.

An Example:

Measure: Active SKU Count
Products dimension has product_key, SKU and Status

In the DSV for the Products dimension, should I add a CASE statement:

CASE WHEN [Status] = 'Active' THEN 1 ELSE 0 END AS [Active Status]

Then create a new SUM measure based on the Active Status column.

OR

Create a new Named Query in the DSV:

SELECT * from Products WHERE Status = 'Active'

Then create a new DISTINCT COUNT measure based on the SKU column from the new Named Query?

These 2 alternatives do not produce the same results. If a given SKU has fact records in Jan, Feb, and Mar, the first approach using SUM would return a value of 3 for Calendar Quarter 1. Then second approach based on a distinct count measure would return 1 (which is probably what you want)

Distinct count measures should be implemented in their own measure group anyway, so if you only ever need the active count then filtering out the non-active records is a good option.

If you need to get distinct counts of both active and inactive SKUs then you would be better off adding the Active flag as an attribute to the product dimension and implementing it as a slowly changing dimension.

|||Darren,

I had a feeling that these 2 alternatives would not produce the same results when I was typing up the question. Perhaps my example scenario wasn't a good one, because the limited testing with my applicable situation yielded the same results.

In any event, I will just go ahead and go forward with the separate measure group with the new Named Query approach.

Thanks!

Best method of checking for duplicate entries in SQL Server

Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.

In my Favorites table, the 3 fields are....

favoriteId (set as primary key)
userId
carId

I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.

1) Check proactively by doing a....
SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)
If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.

2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.

Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?

TIA for your help.

Your best bet IMO would be a derivative of option #1.

If NOT Exists(select favoriteID from favorites where userid = @.x and carid = @.y)
BEGIN
--insert record here
return 0
END
ELSE
BEGIN
return 1
END

where a return of 1 means the record exists

|||

Thanks for the reply, Diamsorn. Is that something called a stored procedure? I'm not terribly familiar with them.... but if that's the only way to go, I'll try to research them. Is there a way to translate that into an inline sql query in my VB code? I've been using the SqlCommand object with an SqlDataReader to get at my queries. Is your method noticeably more efficient that mine? Here's my snippet of code where I proactively check for an existing entry, then I do one of two actions depending on whether or not the record exists. Thanks for helping out an ignorant.

Dim favoriteIdAsInteger
Dim favoriteLookupCmdAsNew SqlCommand _
("SELECT favoriteId FROM Favorites WHERE userId = " & userId &" AND carId = " & carId, dbConnection)
thisReader = favoriteLookupCmd.ExecuteReader()
While (thisReader.Read())
favoriteId = thisReader.GetValue(0).ToString
EndWhile
thisReader.Close()

If (String.IsNullOrEmpty(favoriteId) =False)Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) VALUES (" & userId &", " & carId &")", dbConnection)
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="Error in adding car."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf
EndIf

Thanks.

|||

Its not a stored procedure, but rather the T-SQL that will do the same as what you are doing above inside of a stored procedure.

Either method is fine, doing it in a single stored procedure reduces the number of trips back to the server your app has to make,

Also you reduce the chance for SQL injection attacks.
If your going to go with the method you have above, then your going to want to use parameters to also reduce the chance for SQL injection attacks.

 
Dim favoriteLookupCmdAs New SqlCommand _ ("SELECT favoriteId FROM Favorites WHERE userId = @.useriD AND carId = @.carID", dbConnection)favoriteLookupCmd.Parameters.AddWithValue("@.useriD", userID)favoriteLookupCmd.Parameters.AddWithValue("@.carID", carID)
 

and do the same for your 2nd sql statement as well.|||

INSERT INTO Favorates(UserID,CarID) SELECT @.UserID,@.CarID WHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)

because the check and insert are wrapped into a single SQL Statement, locks are automatically placed on any records it matches in the WHERE clause until the INSERT has completed. This guarantees that you will never insert records into the favorates table that already has the record. You could also do this:

IF NOT EXISTS(...) INSERT ...

but the locks (read/shared) on the records in the exists clause are released prior to the insert, which leaves an opportunity for a record to be inserted between them.

Dim rowsAffectedAsInteger
Dim insertFavoriteCmdAs SqlCommand =New SqlCommand("INSERT INTO Favorites (userId, carId) SELECT @.userid,@.caridWHERE NOT EXISTS(SELECT * FROM Favorates WHEREuserid=@.UserID ANDcarId=@.CarID)", dbConnection)

with insertFavoriteCmd

.Parameters.Add("@.userid",sqldbtype.nvarchar).value=userid

.Parameters.Add("@.carid",sqldbtype.integer).Value=carid

end with
rowsAffected = insertFavoriteCmd.ExecuteNonQuery()
If rowsAffected <> 1Then
addFavStatusLabel.Text ="This car already exists in your favorites list."
Else
addFavStatusLabel.Text ="Car added to favorites successfully."
EndIf

|||

How should i write update query for the same ?

plz help me out of this guys....

Best data type for a range

I want to know what the best datatype is for a situation like this e.g.if I have a field "age" and the data has a range i.e. 22-30, 31-50 etc, what is the best data type to use for this scenario.

Similarly if I have a field that whereby you use a range for example 1-2 in one record but in another you get an integer value of 0 for instance, what again would be the best datatype.

Many thanks

Integers.

|||

can integers support certain characters such as hypens (-) etc

|||

right not sure how to implement this - the data can't go as 21-30 as it will subract the two values, how I would i add this as a range

|||

If you want the column to stroe values in the form 21-30 etc then varchar(<some length>). The second case also varchar. If I understood your situation correctly.

Or maybe if you are trying to store for each row a lower limit and a higher limit for age, then how about having two columns lower_age_limit and higer_age_limit each perhaps of the tinyint datatype. Or a seperate table altogether for the the age limit, something like tblAgeLimit (id int identity(1,1), lower_limit tinyint, upper_limit tinyint) and then linking the id to the table you need.

|||

Master81:
can integers support certain characters such as hypens (-) etc

No. Sorry - I didn't realise that was the value you wanted to store. You would have to use a varchar.