Fail over scenario under Transaction replication

I get 2 questions which need quick answer:
Setup replication between Srv1 DB A (Distributor and publisher) with push
publication to the subscriber Srv2 DB AB
First qestion: many table get identity value set to YES on publisher, these
values are lost in subscriber, but i need to get identical Design of table in
both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one in
publisher side.
How get automatically the identity setup to YES on the subscriber site?
i see many post but no one with concrete explanation, also about sp4?
Second question: as Publisher fail, we will restore the Database from
Subscriber to Publisher.
But if i do so, the replication between the Publisher and subscriber fails
(with some counter msg). Only way is to close publication and create it again.
We want to avoid any manual intervention possible as the customer will just
restore the DB and replication should continue as normal with subscriber.
Any recommandation on the second questions, i see some stored procedure? to
reset the counter. Please more precise solutions.
Mostly if the DB is restore and if you make any modification on the
Publisher side, they are not forwarded to the subscriber.
Waiting your feedback with a major welcome as our customer will used Dual
Sql server 2k. A reply direct to my email will be appreciate.
1) Your best bet is to script out your schema and then push this to the
subscriber using the pre snapshot command. Then for your articles
defaults/table articles select the snapshot tab, and select delete all the
data in the existing table.
2) when you restore your database, recreate your publication and do a
no-sync subscription.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martial" <Martial@.discussions.microsoft.com> wrote in message
news:62916144-CA72-49F1-AC09-4003E0D70ABC@.microsoft.com...
> I get 2 questions which need quick answer:
> Setup replication between Srv1 DB A (Distributor and publisher) with push
> publication to the subscriber Srv2 DB AB
> First qestion: many table get identity value set to YES on publisher,
these
> values are lost in subscriber, but i need to get identical Design of table
in
> both DB, as if Srv 1 fail, we will use DB AB from Srv 2 to replace the one
in
> publisher side.
> How get automatically the identity setup to YES on the subscriber site?
> i see many post but no one with concrete explanation, also about sp4?
> Second question: as Publisher fail, we will restore the Database from
> Subscriber to Publisher.
> But if i do so, the replication between the Publisher and subscriber fails
> (with some counter msg). Only way is to close publication and create it
again.
> We want to avoid any manual intervention possible as the customer will
just
> restore the DB and replication should continue as normal with subscriber.
> Any recommandation on the second questions, i see some stored procedure?
to
> reset the counter. Please more precise solutions.
> Mostly if the DB is restore and if you make any modification on the
> Publisher side, they are not forwarded to the subscriber.
> Waiting your feedback with a major welcome as our customer will used Dual
> Sql server 2k. A reply direct to my email will be appreciate.
|||Hi Paul,
I try this method, now i get my structure table kept in place, but i can not
edit on the table of the Subscriber.
Example.
DB1
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Paul.
I try that, the structure table is identical to original one.
But it's a little worse, cause in case of fail of primary server, can not
modify or
delete or add new item to table.
I used our application and directly to SQL server, i get the msg login
failed for sa, check my Sa password on Subscriber it's fine.
I believe maybe related to MSDTC startup.
Please Help!!!
If i can modifiy on subscriber and add, i solved all my pb.
"Paul Ibison" wrote:

> Have a look at queued updating subscribers. This will
> solve the identity problem and will allow failback
> without having to set up everything again.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It looks like you have set it up as immediate updating
with queued failover. What I was suggesting is just a
queue. Have a look at this article for the source of the
error: http://support.microsoft.com/default.aspx?scid=%
2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
However, I think that you'll have to reinitialize because
on failover if the publisher is not available, your
triggers will prevent any changes being made as they'll
error.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thank you for your feedback, i foudn this article earlier and fix my problem.
But u are right, as tested with my customer if publisher server fail, no
changes are possible on the subscriber which not answer the customer need of
failover system.
So i hope Transaction replication will do or i need to change to Merge?
Or as u mention should i will try the queue updating (subcription) and at
the publication level should i choose both (queue and immediate)?
I get an additional question about the fail over process: the subscriber SQL
DB should time to time be used to restore older database for application
purpose.
My idea is follow:
1. stop all agents on publisher sql server, which i believe should stop
replication to occurs between both SQL.
2. Backup database on subscriber (full one of course)
3. restore old DB for application purpose.
4. restore backup of point 2, once needed.
5. restart agent.
I hope this procedure will continue the replication where it stop, can you
confirm my idea?
I really appreciate your prompt feedback on my question, you are Master of
Replication in point of view.
My last question is the growing size of DB on both SQL, how to ensure i will
not get XXGb of DB.
"Paul Ibison" wrote:

> It looks like you have set it up as immediate updating
> with queued failover. What I was suggesting is just a
> queue. Have a look at this article for the source of the
> error: http://support.microsoft.com/default.aspx?scid=%
> 2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D32 0773
> However, I think that you'll have to reinitialize because
> on failover if the publisher is not available, your
> triggers will prevent any changes being made as they'll
> error.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Just queued on its own would meet your requirements.
Your backup plan looks sound. Also consider the timeframe
involved. You might need to change the transaction
retention period and history retention period and the
subscription expiration time as appropriate if
the 'downtime' lasts any length of time. In the worst
case if you don't do this, commands will be removed from
the distribution database before being propagated to the
subscriber and you'll be left having to reinitialize.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
It works like a dream, thanks for all.
Now i will make some more testing in our office to check HOW to setup
retention period, clean up transaction period, etc...
You have been my 'seifu' like we said in Asia. for master.
"Paul Ibison" wrote:

> Just queued on its own would meet your requirements.
> Your backup plan looks sound. Also consider the timeframe
> involved. You might need to change the transaction
> retention period and history retention period and the
> subscription expiration time as appropriate if
> the 'downtime' lasts any length of time. In the worst
> case if you don't do this, commands will be removed from
> the distribution database before being propagated to the
> subscriber and you'll be left having to reinitialize.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Extremely Poor Query Performance - Identical DBs Different Performance

Hello Everyone,

I have a very complex performance issue with our production database.
Here's the scenario. We have a production webserver server and a
development web server. Both are running SQL Server 2000.

I encounted various performance issues with the production server with a
particular query. It would take approximately 22 seconds to return 100
rows, thats about 0.22 seconds per row. Note: I ran the query in single
user mode. So I tested the query on the Development server by taking a
backup (.dmp) of the database and moving it onto the dev server. I ran
the same query and found that it ran in less than a second.

I took a look at the query execution plan and I found that they we're
the exact same in both cases.

Then I took a look at the various index's, and again I found no
differences in the table indices.

If both databases are identical, I'm assumeing that the issue is related
to some external hardware issue like: disk space, memory etc. Or could
it be OS software related issues, like service packs, SQL Server
configuations etc.

Here's what I've done to rule out some obvious hardware issues on the
prod server:
1. Moved all extraneous files to a secondary harddrive to free up space
on the primary harddrive. There is 55gb's of free space on the disk.
2. Applied SQL Server SP4 service packs
3. Defragmented the primary harddrive
4. Applied all Windows Server 2003 updates

Here is the prod servers system specs:
2x Intel Xeon 2.67GHZ
Total Physical Memory 2GB, Available Physical Memory 815MB
Windows Server 2003 SE /w SP1

Here is the dev serers system specs:
2x Intel Xeon 2.80GHz
2GB DDR2-SDRAM
Windows Server 2003 SE /w SP1

I'm not sure what else to do, the query performance is an order of
magnitude difference and I can't explain it. To me its is a hardware or
operating system related issue.

Any Ideas would help me greatly!

Thanks,
Brian T

*** Sent via Developersdex http://www.developersdex.com ***Brian
Before looking at hardware try running
UPDATE STATISTICS tablename for relevant tables with indexes
and see if it makes a difference to performance

Brian Tabios wrote:
> Hello Everyone,
> I have a very complex performance issue with our production database.
> Here's the scenario. We have a production webserver server and a
> development web server. Both are running SQL Server 2000.
> I encounted various performance issues with the production server with a
> particular query. It would take approximately 22 seconds to return 100
> rows, thats about 0.22 seconds per row. Note: I ran the query in single
> user mode. So I tested the query on the Development server by taking a
> backup (.dmp) of the database and moving it onto the dev server. I ran
> the same query and found that it ran in less than a second.
> I took a look at the query execution plan and I found that they we're
> the exact same in both cases.
> Then I took a look at the various index's, and again I found no
> differences in the table indices.
> If both databases are identical, I'm assumeing that the issue is related
> to some external hardware issue like: disk space, memory etc. Or could
> it be OS software related issues, like service packs, SQL Server
> configuations etc.
> Here's what I've done to rule out some obvious hardware issues on the
> prod server:
> 1. Moved all extraneous files to a secondary harddrive to free up space
> on the primary harddrive. There is 55gb's of free space on the disk.
> 2. Applied SQL Server SP4 service packs
> 3. Defragmented the primary harddrive
> 4. Applied all Windows Server 2003 updates
>
> Here is the prod servers system specs:
> 2x Intel Xeon 2.67GHZ
> Total Physical Memory 2GB, Available Physical Memory 815MB
> Windows Server 2003 SE /w SP1
> Here is the dev serers system specs:
> 2x Intel Xeon 2.80GHz
> 2GB DDR2-SDRAM
> Windows Server 2003 SE /w SP1
> I'm not sure what else to do, the query performance is an order of
> magnitude difference and I can't explain it. To me its is a hardware or
> operating system related issue.
> Any Ideas would help me greatly!
> Thanks,
> Brian T
> *** Sent via Developersdex http://www.developersdex.com ***|||I've seen this before and acutally it's quite common.

The key to your problem is different execution plans.

A SP can have several copies of a execution plan.

a) Different SET statements to the connection
b) You don't call the SP from QA with proper owner prefix (e.g. dbo)
c) In a multitple CPU environment you will have one scheduler (UMS) for
each SPID, and you might experience that you get the same exection plan
until your thread is closed.

Make sure that you update the statistics whenever you experience such
problems. The stats are stored in server, not in the databases.

So what do you do?
If a procedure gets slow, you can recompile the procedure with
sp_recompile. If this doesn't help, use DBCC FREEPROCCACHE and run the
procedure agin.

SP's are often recompiled in an OLTP environment. Common reason is
change in statistics. Sometimes the optimizer makes a poor choice in
execution plan due to variance in the parameteres it recevies when
recompiling. It's smart to look into what parameters are sent to the
procedure and see if there are great changes. Also pay attention to
complex procedures with if-else and case-statements. Keep it simple!

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***

Extremely complex Select statement

Hello,
I'm having real problems defining a SELECT statement for
the following scenario:
I have a Requirements table, each row in this table has
at least 1 row in the RequirementsWeeks table which
defines values for N weeks for each requirement.
Also, each requirement has a product value and a tool
value and each product belongs to a prodCategory and each
tool belongs to a ToolCategory (these associations are
stored in separate tables)
Here's an example of the records I have:
Requirements: RequirementsWeeks:
Product Tool Id ReqId Week Value
A TA 1 1 8 0.20
C TB 2 1 9 0.35
2 8 2.56
2 9 3.52
Products: Tools:
Id ProductName ProCategoryID Id ToolName ToolCatID
1 A 45 1 TA 68
2 B 53 2 TB 68
3 C 45
What i want to have is:
For all pairs product-tool in requirements that matches
ToolCatID And ProdCatID return the sum for week N
For example:
ToolCatID:68 And ProdCatID:45 the sum will return 0.20 +
2.56
And I need this to be done for All posible combinations
of ToolCatID and ProdCatID.
Any thoughts? I don't want to loop through a single SQL
statement that receives toolCat and prodCat as parameters,
I thought there's a way for SQL to manage this.
Thank you a lot for your time! I'm really desperate!Yes, exactly. Thanks a lot for your time, I've already
found a solution, which is to divide the problem in two
parts: The statement to select each row, and the one to
select that for all possible combinations of toolcat and
product cat(which was a very easy select). This seems to
work well but if you have a better idea please let me know.
Thank you so much for your time!! I really appreciate it!
Marcela
>--Original Message--
>So based on your example is this what you want the output
to be
>Toolcatid ProDCatID Sum Week
>68 45 0.2 + 2.56 8
>68 45 0.35+3.52 9
>68 53 0.2 + 2.56 8
>68 53 0.35+3.52 9
>
>"Marcela" <marcela.villalobos@.yahoo.com> wrote in message
>news:057b01c35bb1$af9e9310$a401280a@.phx.gbl...
>> Hello,
>> I'm having real problems defining a SELECT statement
for
>> the following scenario:
>> I have a Requirements table, each row in this table has
>> at least 1 row in the RequirementsWeeks table which
>> defines values for N weeks for each requirement.
>> Also, each requirement has a product value and a tool
>> value and each product belongs to a prodCategory and
each
>> tool belongs to a ToolCategory (these associations are
>> stored in separate tables)
>> Here's an example of the records I have:
>> Requirements: RequirementsWeeks:
>> Product Tool Id ReqId Week Value
>> A TA 1 1 8 0.20
>> C TB 2 1 9 0.35
>> 2 8 2.56
>> 2 9 3.52
>> Products: Tools:
>> Id ProductName ProCategoryID Id ToolName
ToolCatID
>> 1 A 45 1 TA 68
>> 2 B 53 2 TB 68
>> 3 C 45
>>
>> What i want to have is:
>> For all pairs product-tool in requirements that matches
>> ToolCatID And ProdCatID return the sum for week N
>> For example:
>> ToolCatID:68 And ProdCatID:45 the sum will return
0.20 +
>> 2.56
>> And I need this to be done for All posible combinations
>> of ToolCatID and ProdCatID.
>> Any thoughts? I don't want to loop through a single SQL
>> statement that receives toolCat and prodCat as
parameters,
>> I thought there's a way for SQL to manage this.
>> Thank you a lot for your time! I'm really desperate!
>>
>
>.
>

Extracting SQL Query from Report (.rpt) in CR for VS.NET

Hi all,
I am new to Crystal report. I have a scenario in which i need to obtain the sql query from a report (.rpt file).
I am using ReportDocument object but it does not contains the property or method that returns the query. It seems that Report Designer (or report itself may be) stores (constructs) the sql query that reflect the report data [e.g. right click on "Database Field" node in Report Designer and select "Show SQL Query", it displays the query for the report.]
Does any body know how to get that query programmatically?

I would appreciate any sort of help.

Thanks,
AsimHi,
For your information...
have a look at:

http://support.businessobjects.com/library/kbase/articles/c2013145.asp

Kind Regards,
Asim