Hi Guys,
I AM USING DISTRIBUTED TRANSACTION WITH sql server 2000 on
win2k working fine.
but the same transaction not working in win2k3.
i am getting the following error.
Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
The operation could not be performed because the OLE DB
provider 'SQLOLEDB' was unable to begin a distributed
transaction.
[OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
NETWORK ACCESS.
MY CODE IS LIKE THIS.
CREATE PROCEDURE P2( @.NO INT)
AS
BEGIN DISTRIBUTED TRAN
INSERT INTO TESTB VALUES(1,'GH')
EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
COMMIT TRAN
pls advice mecontrol panel, addr emove perograms, add/remove win components, application
server, enable network COM+ access
"bijupg" wrote:
> Hi Guys,
> I AM USING DISTRIBUTED TRANSACTION WITH sql server 2000 on
> win2k working fine.
> but the same transaction not working in win2k3.
> i am getting the following error.
> Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
> The operation could not be performed because the OLE DB
> provider 'SQLOLEDB' was unable to begin a distributed
> transaction.
> [OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
> NETWORK ACCESS.
> MY CODE IS LIKE THIS.
> CREATE PROCEDURE P2( @.NO INT)
> AS
> BEGIN DISTRIBUTED TRAN
> INSERT INTO TESTB VALUES(1,'GH')
> EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
> COMMIT TRAN
> pls advice me
>|||check "Allow other SQL Servers to connect remotely to this SQL Server using
RPC" and "Enforce distributed transactions"
"Aleksandar Grbic" wrote:
> control panel, addr emove perograms, add/remove win components, application
> server, enable network COM+ access
> "bijupg" wrote:
> > Hi Guys,
> >
> > I AM USING DISTRIBUTED TRANSACTION WITH sql server 2000 on
> > win2k working fine.
> > but the same transaction not working in win2k3.
> >
> > i am getting the following error.
> >
> > Server: Msg 7391, Level 16, State 1, Procedure P2, Line 5
> > The operation could not be performed because the OLE DB
> > provider 'SQLOLEDB' was unable to begin a distributed
> > transaction.
> > [OLE/DB provider returned message: New transaction cannot
> > enlist in the specified transaction coordinator. ]
> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> > ITransactionJoin::JoinTransaction returned 0x8004d00a].
> >
> > NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED FOR
> > NETWORK ACCESS.
> >
> > MY CODE IS LIKE THIS.
> > CREATE PROCEDURE P2( @.NO INT)
> > AS
> > BEGIN DISTRIBUTED TRAN
> > INSERT INTO TESTB VALUES(1,'GH')
> > EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
> > COMMIT TRAN
> >
> > pls advice me
> >|||even after this not working.
>--Original Message--
>check "Allow other SQL Servers to connect remotely to
this SQL Server using
>RPC" and "Enforce distributed transactions"
>"Aleksandar Grbic" wrote:
>> control panel, addr emove perograms, add/remove win
components, application
>> server, enable network COM+ access
>> "bijupg" wrote:
>> > Hi Guys,
>> >
>> > I AM USING DISTRIBUTED TRANSACTION WITH sql server
2000 on
>> > win2k working fine.
>> > but the same transaction not working in win2k3.
>> >
>> > i am getting the following error.
>> >
>> > Server: Msg 7391, Level 16, State 1, Procedure P2,
Line 5
>> > The operation could not be performed because the OLE
DB
>> > provider 'SQLOLEDB' was unable to begin a distributed
>> > transaction.
>> > [OLE/DB provider returned message: New transaction
cannot
>> > enlist in the specified transaction coordinator. ]
>> > OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>> > ITransactionJoin::JoinTransaction returned
0x8004d00a].
>> >
>> > NOTE: MSDTC IS RUNNING ON BOTH SERVERS AND ENABLED
FOR
>> > NETWORK ACCESS.
>> >
>> > MY CODE IS LIKE THIS.
>> > CREATE PROCEDURE P2( @.NO INT)
>> > AS
>> > BEGIN DISTRIBUTED TRAN
>> > INSERT INTO TESTB VALUES(1,'GH')
>> > EXEC SHAFEEQVIRTUAL.PUBS.DBO.P1 @.NO
>> > COMMIT TRAN
>> >
>> > pls advice me
>> >
>.
>
Showing posts with label win2k. Show all posts
Showing posts with label win2k. Show all posts
Friday, February 17, 2012
Tuesday, February 14, 2012
Distributed system patterns - SQL Server deployment
DEV ENVIRONMENT: .NET and some COM
DATABASE: SQL Server 2000
CLIENT OS: Win2K, WinXP
SERVER OS: Win2K3
Greetings,
I have a client who insists on having their customers in separate SQL
databases for an online component. I instinctively feel that a consolidated
database for all clients is the "natural" way to implement. Here's the
system setup:
1) Several hundred clients have an n-tier client/server system at THEIR site
(A). They use this system to run their business and serve THEIR customers
(B)
2) We host and operate an online system (C) for these several hundred
clients
that provides an online presence that THEIR customers (B) can use ...
therefore a database now exists at site (A) and at the web site online (C).
These databases get synchronized. Both databases serve the client base (B).
In conclusion, some technical arguments for the one consolidated database at
site (C) are:
i.) Less server resources in terms of database connection pools since these
are usually connect string dependent
ii) Easier reporting (across clients)
iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000, 3000
SQL databases ?)
Proponents for a multiple database scenario have these arguments
i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
client at a time
ii.) Easier to partition if clients ever decided to HOST their own online
presence.
iii) Increased security (perceived or real)
Please provide any feedback or arguments for either side.
Thanks in advance.
Bert Nieves
Software Consultant
bert89@.yahoo.comBert Nieves (bert89@.yahoo.com) writes:
> I have a client who insists on having their customers in separate SQL
> databases for an online component. I instinctively feel that a
> consolidated database for all clients is the "natural" way to implement.
> ...
> In conclusion, some technical arguments for the one consolidated
> database at site (C) are:
> i.) Less server resources in terms of database connection pools since
> these are usually connect string dependent
> ii) Easier reporting (across clients)
> iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000,
> 3000 SQL databases ?)
> Proponents for a multiple database scenario have these arguments
> i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
> client at a time
> ii.) Easier to partition if clients ever decided to HOST their own online
> presence.
> iii) Increased security (perceived or real)
Of these I would consider point ii) of the pro arguments and iii) of the
contra arguments to be the most important.
If you need to do reporting for one client over all their customers, then
one database per customer is not going to be fun. But you could have jobs
which extracts data from the smaller databases into a big reporting
database.
On the other hand, if your client's customers will have access to the
databases themselves, and it is instrumental that these customer cannot
see the slightest of some other customer's data, this a very strong
argument for separate databases. Yes, you can solve this with views,
stored procedures etc, but it takes just one bug to ruin the users'
confidence in the system.
As for scaling, you probably scale better with more databases, because
you can then easily distribute the databases on several boxes.
One thing you don't mention, is that many databases will require more
effort into setting up backup routines etc.
In the end, this is a trade-off between business requirements and cost
of ownership. One big database is probably cheaper - unless the business
requiresments for separating customers from each other is very high.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
DATABASE: SQL Server 2000
CLIENT OS: Win2K, WinXP
SERVER OS: Win2K3
Greetings,
I have a client who insists on having their customers in separate SQL
databases for an online component. I instinctively feel that a consolidated
database for all clients is the "natural" way to implement. Here's the
system setup:
1) Several hundred clients have an n-tier client/server system at THEIR site
(A). They use this system to run their business and serve THEIR customers
(B)
2) We host and operate an online system (C) for these several hundred
clients
that provides an online presence that THEIR customers (B) can use ...
therefore a database now exists at site (A) and at the web site online (C).
These databases get synchronized. Both databases serve the client base (B).
In conclusion, some technical arguments for the one consolidated database at
site (C) are:
i.) Less server resources in terms of database connection pools since these
are usually connect string dependent
ii) Easier reporting (across clients)
iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000, 3000
SQL databases ?)
Proponents for a multiple database scenario have these arguments
i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
client at a time
ii.) Easier to partition if clients ever decided to HOST their own online
presence.
iii) Increased security (perceived or real)
Please provide any feedback or arguments for either side.
Thanks in advance.
Bert Nieves
Software Consultant
bert89@.yahoo.comBert Nieves (bert89@.yahoo.com) writes:
> I have a client who insists on having their customers in separate SQL
> databases for an online component. I instinctively feel that a
> consolidated database for all clients is the "natural" way to implement.
> ...
> In conclusion, some technical arguments for the one consolidated
> database at site (C) are:
> i.) Less server resources in terms of database connection pools since
> these are usually connect string dependent
> ii) Easier reporting (across clients)
> iii) Scaling past several hundred clients seems scary (i.e. 1000, 2000,
> 3000 SQL databases ?)
> Proponents for a multiple database scenario have these arguments
> i.) Separate SQL Server files (MDB) limit risk of data corruption to 1
> client at a time
> ii.) Easier to partition if clients ever decided to HOST their own online
> presence.
> iii) Increased security (perceived or real)
Of these I would consider point ii) of the pro arguments and iii) of the
contra arguments to be the most important.
If you need to do reporting for one client over all their customers, then
one database per customer is not going to be fun. But you could have jobs
which extracts data from the smaller databases into a big reporting
database.
On the other hand, if your client's customers will have access to the
databases themselves, and it is instrumental that these customer cannot
see the slightest of some other customer's data, this a very strong
argument for separate databases. Yes, you can solve this with views,
stored procedures etc, but it takes just one bug to ruin the users'
confidence in the system.
As for scaling, you probably scale better with more databases, because
you can then easily distribute the databases on several boxes.
One thing you don't mention, is that many databases will require more
effort into setting up backup routines etc.
In the end, this is a trade-off between business requirements and cost
of ownership. One big database is probably cheaper - unless the business
requiresments for separating customers from each other is very high.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Subscribe to:
Posts (Atom)