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 RECOMMENDATION FOR A TESTING SCENARIO

guys:
I would like to have your best advise.
We am planning to install Passive/Active SQL server Clustering in the
production environment that includes two identical Dell PowerEdge 6650 dual
processor servers with a External Disk Storage PoweVault 220S. We have
bought two copies of Windows 2003 enterprise (for each one of the servers)
and one only copy of SQL Server 200 enterprise Edition licensed per
processor (I was told that only one copy of this software ins needed in an
Active/Passive mode)
On the other hand, since the configuration above would be in production at
all times, we would like to have similar scenario for development and
testing purposes.I was advised to replicate the same hardware and software
scenario described above, however, as you can see, it would be a costly
endeavor. Each Dell 6650 costs approximately $20,000 (two processors, 8 GB
RAM, 2 MB cache) the External Raid $8,000, the Windows 2003 Enterprise
server software $3,000 (for the two servers), and the SQL Server 200
enterprise software about $24,000 (license for two processor, for just one
server)
Could you please if you see any issue in the production scenario? Are we Ok
using only one copy of SQL server there?
Secondly, do we have any other choice for a development and testing
scenario? Most people recommend that a development and testing scenario
would be, if not identical, at least very similar to the production
scenario. I was planning to get those Dell 6650 server but with a single
processor, only 1 GB of RAM, and 1 MB cache (or even 512 KB). In terms of
software I was also told that one economical approach would be to acquire
the MSDN Universal subscription that allows use software for testing
(Windows and SQL server)
Thirdly, do you have any other (most economical recommendation in terms of
hardware and software for our development and testing scenario? How critical
is that this has to be very similar (or identical) to the production
scenario?
Thanks for all your answers
White
First off, what you are implementing is a single instance cluster, not an
active/passive cluster. It may just look like a term, but there is a very
dramatic difference between the two.
As for licensing SQL Server in a cluster, you have exactly what you need.
The easiest way to tally up licensing for a cluster is to ask how many SQL
Servers you can connect to from an application. In your case, it would be
1.
For the dev/testing environments, you do not need to purchase the Enterprise
Edition of SQL Server. You can use the Developer Edition which gives you
the full functionality of Enterprise Edition without all of the cost and
hardware requirements. This even allows you to simulate a cluster, you just
don't get full clustering functionality. But, you do NOT need to stuff
clusters into your dev/test environments. There is no case that I'm aware
of where a clustered SQL Server behaves differently with respect to an
application than a standalone SQL Server.
My recommendation would be to purchase a Dell 6650 with the external RAID
array. Depending on your testing and development scenario, you can very
easily place BOTH dev and test on the same machine in different SQL Server
instances without collisions. The only real reason to have completely
different systems for the two would be if you are doing a lot of very heavy
performance related work. If not, you can get away with a single machine
with external array + Windows 2003 Server + SQL Server 2000 Developer
Edition.
I can send you my address so you can send a check for the ~$54,000 that I
just saved you.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

best practices on maxinsertcommitsize

Hi,

I wonder if anyone knows what would be the best case scenario for the property 'maxinsertcommitsize' for the sql destination task if I want to load 6m records into a target. Is the best setting 0 (try loading all in one batch) or should I choose a different value for example 1000000 per batch?

Thanks,

Marc

This setting is up to you. How big of a batch do you want to insert? If you encounter an error in the load, do you want to roll back ALL records, or just the batch?

There is no "best practice" because this is user dependent. Everyone's situation is different.|||

Ok, but does it affect performance? For our situation the following applies:

- We don't care how big the batch will be as long as it is optimized for maximum performance
- If we encounter an error in the load it doesn't matter if ALL records roll back or just the batch.

|||I don't think there will be much of a performance difference. The one thing to watch out for in a big batch is the potential for filling up the transaction log.

Try different settings and report your results back to us.|||Actually there may be a performance difference between using batches and not. When SQL Server commits a batch it has to update any affected indexes. 6M records could be a good deal of work. I've seen it take over an hour to commit a batch that size when indexes are involved. By using smaller batches, SQL Server can get started on this work while SSIS is still sending it rows. But that advantage depends on how long your SSIS process takes. If it can generate those 6M in 30 seconds, then giving SQL a head start isn't going to do much good.

Best Practice for this SP Scenario !

Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !

* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)

In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...

There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :

if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
-----------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)

3) it could be done (maybe) using Functions in SQL SERVER...

There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario .... !!!

I'll appreciate your help ...
Thanks in Advance . ! .Sql2k recompiles the entire sp if a recompilation is needed. Thus, it's best to split up the sproc into child sprocs. So, your #2 would be the way to go.

I suggest you read up on this excellent article.

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

Best practice about creating partitions dynamically in AS2000 ?

Does anyone have a good article, link or just input concering the dynamic creation and processing of partitions in a cube.

The scenario where a large amount of data is daily loaded into the data warehouse. In the ETL there is some logic creating a new table when data is comming in for a new month (example: the table Fact_Q1_2007 is created when data is being recieved on 1. januar 2007 and Fact_Q2_2007 is created when data is recieved 1.april and so on)

The question is now - how do i set up the logic to create partitions dynamic in the cube and afterwards make sure that the partitions are being processed successfully.

One approach is to use the Decision Support Objects (DSO) API, which can be invoked from tools which support COM (like DTS):

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

>>

Decision Support Objects Programmer's Reference

Microsoft? SQL Server? 2000 Analysis Services offers substantial opportunity for you to create and integrate custom applications. The server object model, Decision Support Objects (DSO), provides interfaces and objects that can be used with any COM automation programming language

...

>>

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

>>

...

Use the following code to create an object of ClassType clsPartition:

'Assume an object (dsoCube) of ClassType clsCube exists

Dim dsoPartition As DSO.MDStore

Set dsoPartition = dsoCube.MDStores.AddNew("MyPartition")

>>

Typically, you might then clone an existing "template" partition, and update relevant properties like SourceTable:

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

>>

Properties, clsPartition

...

SourceTable

The name of the fact table for the partition.

SourceTableFilter

Contains the WHERE clause of the SQL statement used to determine which source table rows are to be included in the partition.

>>

|||

Thanks

I read that there should be some tools in the SQL Server 2000 Ressource Kit, which lays only on MSDN. But the File Transfer Manager isn't able to download that file ("Application validation failed, transfers are not enabled"), there seems nowhere else to get that kit. Don't know how long microsoft will take to fix their File Transfer Manager......

best install scenario

In the current MSDN Universal disk set, there is a disk for Visual Studio
2005 (beta2) and a disk for SQL Server 2005. If you install VS2005 you get
the April CTP SQL server but without Server Management Studio. If you
install SQL Server 2005, you get VS2005 but without the normal code
development templates. What's the recommended install sequence to get both?
And after several install/uninstall sequences I seem to have lost start menu
links to VS.Net 2003. Can I have VS.Net 2003 and VS.Net 2005 on the same
machine?
tks
RonHi Ron,
Since SQL Server 2005 has not been public released yet, we will redirect
all SQL Server 2005 posts to the newsgroup below
http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005&sl
cid=us
Thanks so much for your understanding.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

best install scenario

In the current MSDN Universal disk set, there is a disk for Visual Studio
2005 (beta2) and a disk for SQL Server 2005. If you install VS2005 you get
the April CTP SQL server but without Server Management Studio. If you
install SQL Server 2005, you get VS2005 but without the normal code
development templates. What's the recommended install sequence to get both?
And after several install/uninstall sequences I seem to have lost start menu
links to VS.Net 2003. Can I have VS.Net 2003 and VS.Net 2005 on the same
machine?
tks
RonHi Ron,
Since SQL Server 2005 has not been public released yet, we will redirect
all SQL Server 2005 posts to the newsgroup below
http://communities.microsoft.com/ne...qlserver2005&sl
cid=us
Thanks so much for your understanding.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

best install scenario

In the current MSDN Universal disk set, there is a disk for Visual Studio
2005 (beta2) and a disk for SQL Server 2005. If you install VS2005 you get
the April CTP SQL server but without Server Management Studio. If you
install SQL Server 2005, you get VS2005 but without the normal code
development templates. What's the recommended install sequence to get both?
And after several install/uninstall sequences I seem to have lost start menu
links to VS.Net 2003. Can I have VS.Net 2003 and VS.Net 2005 on the same
machine?
tks
Ron
Hi Ron,
Since SQL Server 2005 has not been public released yet, we will redirect
all SQL Server 2005 posts to the newsgroup below
http://communities.microsoft.com/new...lserver2005&sl
cid=us
Thanks so much for your understanding.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Best design for edit tracking?

Hey all,
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.
What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:
1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.
My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a web dev question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.OK, the quickest solution to this goest something like this...
Add two rows to your core table, the first is a DateTime with a Default
constraint of GetDate(). The second column is used to identify who made the
change.
Next when update one of the rows, you need to simply include the identifier
of the person who made the change.
Next when you read the table you need to read and compare two rows. The
most recent row, and the second most recent row. This provides the
information about what changed - as a freebie you'll gain access to the old
version of the row.
Another approach would be to have two tables. The first is the core table
as it is now. The second table contains a flag for each column in your core
table, the DateTime column (as above) and the user identifier column (as
above). This time when you edit the row, you also place a new row into this
table settings the flags for the columns which were altered. Don't forget
to include a foreign key back to your core table. When you select the row
from the core table you can also join on your change map, selecting the
max(datetime) and this will tell what columns were changed in the last edit.
But it won't tell you the values that they were before. There is a big
advantage in this method as your core table doesn't need to be altered.
Regards
Colin Dawson
www.cjdawson.com
"roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
> Hey all,
> This is a general question. Here's my scenario: We have a legacy
> database. The core table within contains almost 4 million records in
> SQL Server. Currently we have a web front end which allows uses to
> search through the database for the information they need.
> What the client wants is a web front end which allows some users to
> edit the core table above. No problem. What the client also wants is
> for the non-editable web front end (used for research) to display (via
> colored text) which records have been edited. Example:
> 1.) Jimmy edits records x, y, and z in coretable1 using the editing
> web app.
> 2.) Jane comes along and uses the research web app to hunt down some
> records. She views records s through z. In her view she notices that
> certain cells in rows x, y, and z are colored red. This tells her that
> Jimmy has edited those specific fields in those specific rows.
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
>|||Colin
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
I think you menat "add to columns", and it is worth mentioning that with
this solutuin you will have to write a trigget on that table in order to
track changes
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
> OK, the quickest solution to this goest something like this...
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
> Next when update one of the rows, you need to simply include the
> identifier of the person who made the change.
> Next when you read the table you need to read and compare two rows. The
> most recent row, and the second most recent row. This provides the
> information about what changed - as a freebie you'll gain access to the
> old version of the row.
> Another approach would be to have two tables. The first is the core table
> as it is now. The second table contains a flag for each column in your
> core table, the DateTime column (as above) and the user identifier column
> (as above). This time when you edit the row, you also place a new row
> into this table settings the flags for the columns which were altered.
> Don't forget to include a foreign key back to your core table. When you
> select the row from the core table you can also join on your change map,
> selecting the max(datetime) and this will tell what columns were changed
> in the last edit. But it won't tell you the values that they were before.
> There is a big advantage in this method as your core table doesn't need to
> be altered.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
> news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
>|||oops, I did mean columns yes.
A trigger won't really be able to help as you'll need to supply extra
information than what is stored in the original table. It would be better
to use a Stored procedure and directly enter the data into the new columns.
Of course, the exception to this is that if the application connects to SQL
using seperate usernames, it is possible to use the @.@.User in a trigger to
accomplish the same result. With the applications that my company creates,
this is not possible as they alway connect with the same user (connection
pooling)
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSloXSxdGHA.4108@.TK2MSFTNGP03.phx.gbl...
> Colin
> I think you menat "add to columns", and it is worth mentioning that with
> this solutuin you will have to write a trigget on that table in order to
> track changes
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
>|||On 13 May 2006 08:30:37 -0700, roy.@.nderson@.gm@.il.com wrote:

>Hey all,
>This is a general question. Here's my scenario: We have a legacy
>database. The core table within contains almost 4 million records in
>SQL Server. Currently we have a web front end which allows uses to
>search through the database for the information they need.
>What the client wants is a web front end which allows some users to
>edit the core table above. No problem. What the client also wants is
>for the non-editable web front end (used for research) to display (via
>colored text) which records have been edited. Example:
>1.) Jimmy edits records x, y, and z in coretable1 using the editing
>web app.
>2.) Jane comes along and uses the research web app to hunt down some
>records. She views records s through z. In her view she notices that
>certain cells in rows x, y, and z are colored red. This tells her that
>Jimmy has edited those specific fields in those specific rows.
>My question is: what is the most efficient way to track these column
>specific edits so that my web app can display them? This may seem like
>a web dev question, but the reality is that my web apps have to
>interact with SQL Server so if anyone has any input, I'd love to hear
>it! Thanks.
Hi Roy,
This is impossible to answer, because the requirements are incomplete.
For instance, what happens if Jimmy edits rows x, y, and z (as in your
example), then Joan edits rows w and x, then Jimmy edits z another time
and then Jane views rows s through z. What columns in what rows have to
be marked as "changed"?
Another question - what if Jimmy edits rows x, y, and z; then nothin
happens for a long time. After a year, Jane looks at rows s through z.
Should Jimmy's changes still be marked?
Hugo Kornelis, SQL Server MVP|||>
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
There are several ways to accomplish that. Do you want your system to
be optimized for retrieval of the current version but support
occasional drill down into editing history. Or do you want to optimize
retrieving history of edits and are ready to pay the price of slowing
down retrieval of current version?