Can two SQL server clusters have mirrored data or near real-time?

We currently have a w2003 SQL cluster and would like to add a second cluster
to the infrastructure. Their plan is to have one online serving customers so
the other cluster may have the db restored... Is there a way to get the
databases on both clusters mirrored? We have the cluster to provide HA, I may
need a big hammer to protect the data from developers fingers!
Any suggestions?
Thanks,
Rodney
Rodney,
You might consider log shipping. Here is an article from SQL Server
magazine by Ron Talmage that you might find interesting.
http://tinyurl.com/46h45
Also, transactional replication is used by some to keep another server
refreshed for queries, reporting, etc.
Narayan Kondreddi has a replication FAQ at
http://vyaskn.tripod.com/repl_ques.htm
Russell Fields
"Rodney" <Rodney@.discussions.microsoft.com> wrote in message
news:8A87A93D-889F-477A-9209-C3D5E1EE32F7@.microsoft.com...
> We currently have a w2003 SQL cluster and would like to add a second
cluster
> to the infrastructure. Their plan is to have one online serving customers
so
> the other cluster may have the db restored... Is there a way to get the
> databases on both clusters mirrored? We have the cluster to provide HA, I
may
> need a big hammer to protect the data from developers fingers!
> Any suggestions?
> Thanks,
> Rodney

Can total be moved to top of column rather than bottom

Standard newbie disclaimer: I have searched the archives and this is
my second week with the tool, so my apologies if this is a rather
simple question or I am not asking the question properly.
My team has received a specification for a report where the customers
would like to see column totals at the top of the column, rather than
SSRS's default of bottom. I am new to banded report writing, so I am
hoping this is fairly simple. Using BusinessObjects, I would have
inserted a row at the top of the column and then copied the formula
from the total (or subtotal) line up to the line I had just inserted
(then deleted the row at the bottom).
Is there similar functionality with SRSS?
Kind regards,
Steve HaffnerYes, it is possible.
Option-1: If you want to show the total besides the group name: Ask them to
add the group Header(if not available). besides the group name table cell ,
write a expression like =sum(field!columnname.value)
Option-2: If you want to show the total in top of the group name:
right click on table row(Group row) and click on insert row above. and write
a expression like ="Group Total : " & sum(field!columnname.value)
Regards,
Sriman.
"shaffner" wrote:
> Standard newbie disclaimer: I have searched the archives and this is
> my second week with the tool, so my apologies if this is a rather
> simple question or I am not asking the question properly.
> My team has received a specification for a report where the customers
> would like to see column totals at the top of the column, rather than
> SSRS's default of bottom. I am new to banded report writing, so I am
> hoping this is fairly simple. Using BusinessObjects, I would have
> inserted a row at the top of the column and then copied the formula
> from the total (or subtotal) line up to the line I had just inserted
> (then deleted the row at the bottom).
> Is there similar functionality with SRSS?
> Kind regards,
> Steve Haffner
>|||On Apr 5, 10:48 am, "shaffner" <steve_haff...@.hotmail.com> wrote:
> Standard newbie disclaimer: I have searched the archives and this is
> my second week with the tool, so my apologies if this is a rather
> simple question or I am not asking the question properly.
> My team has received a specification for a report where the customers
> would like to see column totals at the top of the column, rather than
> SSRS's default of bottom. I am new to banded report writing, so I am
> hoping this is fairly simple. Using BusinessObjects, I would have
> inserted a row at the top of the column and then copied the formula
> from the total (or subtotal) line up to the line I had just inserted
> (then deleted the row at the bottom).
> Is there similar functionality with SRSS?
> Kind regards,
> Steve Haffner
This can be done similarly in SSRS. You can either insert a row/group
in the top of a table control and add something like the following as
the expression:
=Sum(Fields!FieldName.Value)
Also, you can do the totaling in the stored procedure/query that is
sourcing the report and just pass it to the report and create a
textbox control at the top of the table control and include it there.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Can Timestamp column value overflow?

Hi,
I am having a table with a timestamp column. The data in this table is
assumed to accumulate over a period of time. On an avarage every second a
row is inserted into this table.
I using time stamp column for two reasons:
- Ensure that the value of timestamp is ever incrementing.
- Time stamp value should be unique.
Issue: Is there any probability of timestamp getting overflow over a period
of time? If there is a overflow what is the behavior of SQL Server in such
cases?
If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique value for 8 bytes size column.
A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
Thanks in advance.
Pushkar> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
I don't remember the exact calculation, but I think you have to do 10,000
inserts a second for 122 years or something like that, to exceed the upper
bound of a BIGINT. So, you're probably safe. If you have anything other
than this table in the database, you're going to run out of available disk
space on the planet before you use up all of the unique, ever-incrementing
BIGINT values available to you.
Just my opinion.
A|||Consider using an identity column of datatype 4 byte int.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is
> assumed to accumulate over a period of time. On an avarage every second a
> row is inserted into this table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a
> period of time? If there is a overflow what is the behavior of SQL Server
> in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
> incrementing unique value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
>
> Thanks in advance.
> Pushkar
>|||> Issue: Is there any probability of timestamp getting overflow over a period of time? If t
here is a
> overflow what is the behavior of SQL Server in such cases?
The way I remember it:
Even when this was internally a 6 byte size (some earlier version), you coul
d do something like 100
transactions per second for over 100 years before overflow. And it is now 8
byte.
And well before such a theoretic overflow would happen, SQL Server would log
an error and not permit
any more updates in the database, would such a (theoretic) overflow occur. T
his I recall reading in
Books Online, some earlier version.
OK, did some calculations. See code below. If you do 1 transaction per secon
d, it will "overflow"
after 146,235,604,338 years. Or, put another way, if you do 1 million transa
ctions per second, it
will overflow after 146,235 years. Say you plan for a life span of 100 years
for the database, you
would have to do 1 billion transactions per second. TSQL calculations:
DECLARE @.noPossibleValues numeric(38,0)
DECLARE @.secondsPerYear int
SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
SET @.secondsPerYear = 60*60*24*365
SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
YearsIfMillionTransactionsPerSecond
And here is some old text that I found on the subject:
"Maximum Value
--
Timestamps increase until the maximum value that can be stored in 6
bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached,
the database
will not permit any more updates.
A 935 warning message is generated when there are only 1,000,000
timestamp values left in the database.
The only way to start over is to copy out all of the data with BCP
and to re-create the database; dumping and restoring will not help.
This is not a major concern because at 100 transactions per second,
2**48 will not wrap for more than 100 years."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.p
hx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is ass
umed to accumulate over
> a period of time. On an avarage every second a row is inserted into this
table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a perio
d of time? If there is a
> overflow what is the behavior of SQL Server in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique
> value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
>
> Thanks in advance.
> Pushkar
>|||Oops, I did 2**62 instead of 2**64. Means my numbers were on the conservativ
e side... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
> The way I remember it:
> Even when this was internally a 6 byte size (some earlier version), you co
uld do something like
> 100 transactions per second for over 100 years before overflow. And it is
now 8 byte.
> And well before such a theoretic overflow would happen, SQL Server would l
og an error and not
> permit any more updates in the database, would such a (theoretic) overflow
occur. This I recall
> reading in Books Online, some earlier version.
> OK, did some calculations. See code below. If you do 1 transaction per sec
ond, it will "overflow"
> after 146,235,604,338 years. Or, put another way, if you do 1 million tran
sactions per second, it
> will overflow after 146,235 years. Say you plan for a life span of 100 yea
rs for the database, you
> would have to do 1 billion transactions per second. TSQL calculations:
> DECLARE @.noPossibleValues numeric(38,0)
> DECLARE @.secondsPerYear int
> SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
> SET @.secondsPerYear = 60*60*24*365
> SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
>
> SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
> YearsIfMillionTransactionsPerSecond
>
> And here is some old text that I found on the subject:
> "Maximum Value
> --
> Timestamps increase until the maximum value that can be stored in 6
> bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached
, the database
> will not permit any more updates.
> A 935 warning message is generated when there are only 1,000,000
> timestamp values left in the database.
> The only way to start over is to copy out all of the data with BCP
> and to re-create the database; dumping and restoring will not help.
> This is not a major concern because at 100 transactions per second,
> 2**48 will not wrap for more than 100 years."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
>|||Thanks a lot. Now I can safely use it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uurqw78UGHA.4660@.tk2msftngp13.phx.gbl...
> Oops, I did 2**62 instead of 2**64. Means my numbers were on the
> conservative side... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
>sql

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.