Do I really need a snapshot (to initialize transactional replication, in SQL2000)?

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription Turn off distribution agent Set the publisher to "sync with backup" Backup the publisher, full then log Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on) Turn off "sync with backup" Restore the full and tran log backups to new subscriber db Create subscriber stored procs in subscriber Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

OK, I just did a search and came across this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320499

However this method still requires a brief time in single user mode (ie killing all connections), whereas my method doesn't. I just don't like that my method involves deleting the MSrepl_ tables...

DMO & SQL 2005

Will SQL 2005 still have SQLDMO?
Will the objects have the same name as with SQL7/SQL2000?
dirk.I believe it will be supported for backward compatibility but the new
technology is known as SMO.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
news:elhsDVSZEHA.3664@.TK2MSFTNGP12.phx.gbl...
Will SQL 2005 still have SQLDMO?
Will the objects have the same name as with SQL7/SQL2000?
dirk.|||Yes, there will be SQLDMO 9 shipping in SQL Server 2005. It will work at
the 8.0 level against a SQL Server 2005 system, so no new features will be
exposed, but it will continue to function.
--
Richard Waymire, MCSE, MCDBA
This posting is provided ?AS IS? with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23Z1PiZSZEHA.3112@.TK2MSFTNGP09.phx.gbl...
>I believe it will be supported for backward compatibility but the new
> technology is known as SMO.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
> news:elhsDVSZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Will SQL 2005 still have SQLDMO?
> Will the objects have the same name as with SQL7/SQL2000?
>
> dirk.
>|||So it will use the same objects/syntax as those for SQL2000 then?
dirk;
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:uV1sFoSZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> Yes, there will be SQLDMO 9 shipping in SQL Server 2005. It will work at
> the 8.0 level against a SQL Server 2005 system, so no new features will be
> exposed, but it will continue to function.
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23Z1PiZSZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> >I believe it will be supported for backward compatibility but the new
> > technology is known as SMO.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
> > news:elhsDVSZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Will SQL 2005 still have SQLDMO?
> > Will the objects have the same name as with SQL7/SQL2000?
> >
> >
> >
> > dirk.
> >
> >
>|||Yes.
--
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
news:eWbpZpTZEHA.2444@.tk2msftngp13.phx.gbl...
> So it will use the same objects/syntax as those for SQL2000 then?
>
> dirk;
> "Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
> news:uV1sFoSZEHA.3144@.TK2MSFTNGP12.phx.gbl...
>> Yes, there will be SQLDMO 9 shipping in SQL Server 2005. It will work at
>> the 8.0 level against a SQL Server 2005 system, so no new features will
>> be
>> exposed, but it will continue to function.
>> --
>> Richard Waymire, MCSE, MCDBA
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:%23Z1PiZSZEHA.3112@.TK2MSFTNGP09.phx.gbl...
>> >I believe it will be supported for backward compatibility but the new
>> > technology is known as SMO.
>> >
>> > --
>> > Tom
>> >
>> > ---
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> > SQL Server MVP
>> > Columnist, SQL Server Professional
>> > Toronto, ON Canada
>> > www.pinnaclepublishing.com/sql
>> >
>> >
>> > "Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
>> > news:elhsDVSZEHA.3664@.TK2MSFTNGP12.phx.gbl...
>> > Will SQL 2005 still have SQLDMO?
>> > Will the objects have the same name as with SQL7/SQL2000?
>> >
>> >
>> >
>> > dirk.
>> >
>> >
>>
>
>|||Great thanks.
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:eNDWDJUZEHA.1048@.tk2msftngp13.phx.gbl...
> Yes.
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
> news:eWbpZpTZEHA.2444@.tk2msftngp13.phx.gbl...
> > So it will use the same objects/syntax as those for SQL2000 then?
> >
> >
> > dirk;
> >
> > "Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
> > news:uV1sFoSZEHA.3144@.TK2MSFTNGP12.phx.gbl...
> >> Yes, there will be SQLDMO 9 shipping in SQL Server 2005. It will work
at
> >> the 8.0 level against a SQL Server 2005 system, so no new features will
> >> be
> >> exposed, but it will continue to function.
> >>
> >> --
> >> Richard Waymire, MCSE, MCDBA
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> >> news:%23Z1PiZSZEHA.3112@.TK2MSFTNGP09.phx.gbl...
> >> >I believe it will be supported for backward compatibility but the new
> >> > technology is known as SMO.
> >> >
> >> > --
> >> > Tom
> >> >
> >> > ---
> >> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >> > SQL Server MVP
> >> > Columnist, SQL Server Professional
> >> > Toronto, ON Canada
> >> > www.pinnaclepublishing.com/sql
> >> >
> >> >
> >> > "Dirk" <dirk@.nospam_to_remove_ofcourse.woodstone.nu> wrote in message
> >> > news:elhsDVSZEHA.3664@.TK2MSFTNGP12.phx.gbl...
> >> > Will SQL 2005 still have SQLDMO?
> >> > Will the objects have the same name as with SQL7/SQL2000?
> >> >
> >> >
> >> >
> >> > dirk.
> >> >
> >> >
> >>
> >>
> >
> >
> >
>

Distributing SQL2000 Database

Is it possible to encrypt a database so that clients may
not view the table structure, relationships,triggers,
functions, etc.? Objective is to protect the back-end
design from being exposed.You can encrypt sps, udf and triggers, but I don't think you can select
without seeing the structure
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mir Zaman" <anonymous@.discussions.microsoft.com> wrote in message
news:9bc601c3b739$dfbed3f0$a601280a@.phx.gbl...
> Is it possible to encrypt a database so that clients may
> not view the table structure, relationships,triggers,
> functions, etc.? Objective is to protect the back-end
> design from being exposed.

Distributed Transactions between SQL2005 and SQL2000

Hi there,

We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONE\SQL2000' and 'YELLOWSTONE\SQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONE\SQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONE\SQL2000', I get the following error:

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -

Ok, Figured it out. I needed to define the Server type as 'SQL Server' instead of 'Other' and then picking the SQL Server connector. Ooops!

DISTRIBUTED TRANSACTION

Hi,
I have two sql servers with SQL2000 service pack 3 which are linked by the "
Link Server". When i use the "begin tran" (distributed transaction) in store
d procedure, i am getting the following error.
"Server: Msg 8525, Level 16, State 1, Line 1
Distributed transaction completed. Either enlist this session in a new trans
action or the NULL transaction. "
The following article talks about this problem.
http://support.microsoft.com/?kbid=834849
But, both servers are SQL 2000 in my case. Any help?
Thanks,
VijayHi, i'm going this trouble but the versions are different. Server A has Sql
server 2000 enterprise edition and the server B has Sql Server 7.0, both
servers are linked properly and running msdtc. No changes were made to their
configurations. If I run the sentence with Begin distributed tran and commin
distributed tran it end right but using that statement it fails with the
error commented is this post.
Any ideas?
"Vijay" wrote:

> Hi,
>
> I have two sql servers with SQL2000 service pack 3 which are linked by the
"Link Server". When i use the "begin tran" (distributed transaction) in sto
red procedure, i am getting the following error.
>
> "Server: Msg 8525, Level 16, State 1, Line 1
> Distributed transaction completed. Either enlist this session in a new tra
nsaction or the NULL transaction. "
>
> The following article talks about this problem.
> http://support.microsoft.com/?kbid=834849
> But, both servers are SQL 2000 in my case. Any help?
>
> Thanks,
> Vijay
>
>

distributed transaction

Hi everyone,
I've 2 Windows 2000 server running each own instance of SQL2000. I've setup
both linked servers @. both end.
At server A, it'll call a sp in server B, whereby this sp will update server
B tables based on server A's data. And the server A table A will trigger ba
ck to server B.
Server_A call ServerB.sp
|
V
store procedure @. serverB
insert into serverB.dbB.dbo.tableB
select * from serverA.dbA.dbo.tableA where key=X
|
V
serverB.dbB.dbo.tableB trigger back to serverA
if record not found in serverA
insert into serverA
else
update into server A
However I encountered error stating:
Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_serve
r, Line 112
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransac
tion returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the spec
ified transaction coordinator. ]
I found out if I drop the trigger, the sp will perform correctly. But the pr
oblem is I need to keep the trigger.
Can someone enlighten me on this ?
thanks in advance
KristeHi
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
Regards
Mike
"Kriste L" wrote:

> Hi everyone,
> I've 2 Windows 2000 server running each own instance of SQL2000. I've setu
p both linked servers @. both end.
> At server A, it'll call a sp in server B, whereby this sp will update serv
er B tables based on server A's data. And the server A table A will trigger
back to server B.
> Server_A call ServerB.sp
> |
> V
> store procedure @. serverB
> insert into serverB.dbB.dbo.tableB
> select * from serverA.dbA.dbo.tableA where key=X
> |
> V
> serverB.dbB.dbo.tableB trigger back to serverA
> if record not found in serverA
> insert into serverA
> else
> update into server A
>
> However I encountered error stating:
> Server: Msg 7391, Level 16, State 1, Procedure sp_update_across_linked_ser
ver, Line 112
> The operation could not be performed because the OLE DB provider 'SQLOLEDB
' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTrans
action returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the sp
ecified transaction coordinator. ]
> I found out if I drop the trigger, the sp will perform correctly. But the
problem is I need to keep the trigger.
> Can someone enlighten me on this ?
> thanks in advance
> Kriste
>|||I've checked all the necessary things,
- use the dtcping, the 2 servers' rpc server and reverse binding are ok in
both direction.
- there's no firewall in both servers
- in the sp and trigger, SET XACT_ABORT ON and SET REMOTE_PROC_TRANSACTIONS
OFF
in my situation, is the trigger causing a loopback operations?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:75A495AD-7C8F-4B82-8E0C-C72C68609339@.microsoft.com...
> Hi
>
http://groups-beta.google.com/group...=UTF-8&oe=UTF-8
> Regards
> Mike
> "Kriste L" wrote:
>
setup both linked servers @. both end.
server B tables based on server A's data. And the server A table A will
trigger back to server B.
sp_update_across_linked_server, Line 112
'SQLOLEDB' was unable to begin a distributed transaction.
ITransactionJoin::JoinTransaction returned 0x8004d00a].
specified transaction coordinator. ]
the problem is I need to keep the trigger.