3 Snapshot Replication Questions

Hello,
I have a couple questions regarding Snapshot replication. Ive looked at the
full docs (v. SQL 2005) and understand everything except Im not too clear on
these.
1. While a snapshot of a publication is being applied to a subscriber, are
the replicated tables available in read-only mode with the existing data on
the subscriber? or do they get emptied out first and become unavailable until
the snapshot is applied?
2. Does the subscriber's transaction log grow everytime a new snapshot is
applied? Or does it remain unchanged? For example, if the publisher made
100,000 new transactions to a table. Then it gets replicated to the
subscriber. Wil lthose 100k new transactions appear in the subscriber's
transaction log?
3. When a snapshot is applied to a subscriber table that is full-text
indexed, why does the replication remove the full-text indexing on the table?
Is there a workaround for this to leave the full-text indexing as-is?
Johnny
Johnny,
when a snapshot is applied, by default the original table is dropped first.
This explains why the table becomes unavailable to subscribers until the
process has finished, and why the FTI is removed. In my reinitializations, I
have a post-snapshot script to add the FTI. If you want continuous access
for the subscribers (and no table-locks on the publisher, transactional
replication might be more suitable.
When the snapshot is applied at the subscriber, this has no relationship to
the transactions required to populate the table on the publisher. The data
is applied at the subscriber in one hit using odbcbcp. I haven't examined
the transaction log at the subscriber using log explorer, but I would expect
to see just one transaction.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you.
"Paul Ibison" wrote:

> Johnny,
> when a snapshot is applied, by default the original table is dropped first.
> This explains why the table becomes unavailable to subscribers until the
> process has finished, and why the FTI is removed. In my reinitializations, I
> have a post-snapshot script to add the FTI. If you want continuous access
> for the subscribers (and no table-locks on the publisher, transactional
> replication might be more suitable.
> When the snapshot is applied at the subscriber, this has no relationship to
> the transactions required to populate the table on the publisher. The data
> is applied at the subscriber in one hit using odbcbcp. I haven't examined
> the transaction log at the subscriber using log explorer, but I would expect
> to see just one transaction.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

3 Node W2K3 SQL 2005 Cluster

All,
I've setup a Windows 2003 Enterprise Edition cluster of 3 nodes.
Everything is attached to a san (CX500).
Just installed SQL 2005 and all works fine.
Is it possible to have this cluster with 2 active nodes and 1 passive
node?
i've setup a drive (r:\) and it's online but only avaiable from 1
resource. the other nodes sees it but can't access it. I want to setup
a cluster where 2 nodes can access the drive (to share sql databases)
and 1 node access it when it failovers..
is this a good setup or not? should i use 2 or 4 nodes instead of 3?
Or is it possible to add a SQL cluster resources to the same storage?
Also is it possible to setup a load balanced sql 2005 cluster? where i
can have 3 active nodes load balanced and also failover when 1 node
goes down?
Hope someone can help me out..
Thanx in advance.

> Is it possible to have this cluster with 2 active nodes and 1 passive
> node?
Yes, but not in the way you want, you can create another SQL instance and
run this on the other node. Still this instance has its own databases and
its own disks.

> i've setup a drive (r:\) and it's online but only avaiable from 1
> resource. the other nodes sees it but can't access it. I want to setup
> a cluster where 2 nodes can access the drive (to share sql databases)
> and 1 node access it when it failovers..
No, at any given time only ONE node can access a disk ( the node where the
cluster resource group is online ). You may not access an NTFS volume from
more than one server at any given time. This is an NTFS limitation. Hence
clustering is called "shared nothing" cluster model.

> Also is it possible to setup a load balanced sql 2005 cluster? where i
> can have 3 active nodes load balanced and also failover when 1 node
> goes down?
Same as above, you can have multiple instances running, but each of these
instances have their own disks and databases.

> Hope someone can help me out..
> Thanx in advance.
>
Rgds,
Edwin.

2GB Limit

What happens when an SQL2K MSDE database reaches 2GB? I've actually seen
MSDE databases reach that size and nothing happened.
Thanks,
Ademar.
hi Ademar,
Ademar Nunes wrote:
> What happens when an SQL2K MSDE database reaches 2GB? I've actually
> seen MSDE databases reach that size and nothing happened.
when you exceed that limit, nex time the Storage Engine requires a file
allocation beyond the allocated space, thus trying expanding the data file,
an exception will be raised..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Maybe the 2GB+ installation I'm referring to is actually a Personal edition
and not MSDE.
I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
tell me if it is MSDE.
Thanks,
Ademar.
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3o8jmjF4mu4pU1@.individual.net...
> hi Ademar,
> Ademar Nunes wrote:
> when you exceed that limit, nex time the Storage Engine requires a file
> allocation beyond the allocated space, thus trying expanding the data
> file, an exception will be raised..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Hi,
If it is Personal edition; there is no db size limitation. See details in
below URL
http://msdn.microsoft.com/library/?u...asp?frame=true
Thanks
Hari
SQL Server MVP
"Ademar Nunes" <anunes@.myemail.com> wrote in message
news:%23VOAP38sFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Maybe the 2GB+ installation I'm referring to is actually a Personal
> edition and not MSDE.
> I'll run the SELECT @.@.VERSION statement on it and I'm assuming that will
> tell me if it is MSDE.
> Thanks,
> Ademar.
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3o8jmjF4mu4pU1@.individual.net...
>

2005 remote connection problem (yes, I have enabled TCP/IP)

I've gone through about 30+ google results looking for the magic answer to
this problem. I have a brand new machine with a brand new install of Sql
Server 2005 express. I am trying to connect via SQLCMD in a command window
with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
the ip is my local machine and the machine that SS2005 Express is installed
on.
-U is a login that I created. I have set authentication to mixed mode
Not sure what else to try. Anyone have any hints?
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
>
By default SqlExpress uses an instance name of SQLEXPRESS. Your connection
info would be correct for the default instance, not a named instance.
Try
SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
Also to user the instance name you need the SQL Browser service started.
And double check in Computer Management\SQL Server Configuration
Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
David
|||Have you enabled remote connections? Yes, you have to explicitly enable it
to accept connections from external machines. Doesn't matter what libraries
or connection strings you are sending. If remote connections are not
enabled, the only thing that any SQL Server 2005 database will respond to is
something from the machine it resides on.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"sklett" <sklett@.mddirect.com> wrote in message
news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> I've gone through about 30+ google results looking for the magic answer to
> this problem. I have a brand new machine with a brand new install of Sql
> Server 2005 express. I am trying to connect via SQLCMD in a command
> window with the following info: SQLCMD -S 192.168.1.103 -U **** -P ******!
> the ip is my local machine and the machine that SS2005 Express is
> installed on.
> -U is a login that I created. I have set authentication to mixed mode
> Not sure what else to try. Anyone have any hints?
>
|||Hi Michael,
I have enabled them the only way I know how, what I did was use "SQL Server
Surface Are Configuration" app and checked the "Using TCP/IP only" option
under the "Local and remote connections" section. This is one of the first
things that I did. I should have mentioned that in my original post,
apologies.
Any other ideas?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:u%23XwyS0NGHA.3144@.TK2MSFTNGP11.phx.gbl...
> Have you enabled remote connections? Yes, you have to explicitly enable
> it to accept connections from external machines. Doesn't matter what
> libraries or connection strings you are sending. If remote connections
> are not enabled, the only thing that any SQL Server 2005 database will
> respond to is something from the machine it resides on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
>
|||Hi David,
I have tried to specify the name in the address, it doesn't make a
difference I'm afraid.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmHC9Q0NGHA.2668@.tk2msftngp13.phx.gbl...
> "sklett" <sklett@.mddirect.com> wrote in message
> news:%23s3PyC0NGHA.3908@.TK2MSFTNGP10.phx.gbl...
> By default SqlExpress uses an instance name of SQLEXPRESS. Your
> connection info would be correct for the default instance, not a named
> instance.
> Try
> SQLCMD -S 192.168.1.103\SQLEXPRESS -U **** -P ******!
>
> How to: Connect to the Database Engine Using sqlcmd.exe
> http://msdn2.microsoft.com/en-us/library(d=robot)/ms188247.aspx
> Also to user the instance name you need the SQL Browser service started.
> And double check in Computer Management\SQL Server Configuration
> Manager\Protocols for SQLEXPRESS TCP/IP is enabled for that IP interface.
> David
>

2005 Notification Services - problems...

Can somebody help? I'm simply trying to do the built in tutorials. I
have 2005 Dev. Edition installed on my PC, and I've reached this step
in the tutorial process...(Registering, Enabling, and Starting the
Instance)
http://msdn2.microsoft.com/en-us/library/ms170375.aspx
The service will not start, and when I check the event logs, I have
this error....
The Notification Services instance encountered an error in one of its
components and must stop.
EventParameters:
Instance Name: Tutorial
Problem Description: The database was created with or upgraded to a
different edition of Notification Services. Use the Notification
Services edition that the database expects.
Notification Services Edition: Developer Edition
Database Edition: Standard Edition
I'm not really comprehending why the database edition says Standard,
and why my NS edition is Developer, when I installed Dev. Edition on my
PC.
My first suggestion is to make sure you provided the correct SQL Server
instance name when you created the new instance and when you registered the
instance. If those are correct, can you run the following query and let me
know what it reports back?
SELECT @.@.VERSION;
On my Dev Edition instance, here's what it returns:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Diane Larsen [MSFT]
Technical Writer
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1133962720.896823.230160@.g44g2000cwa.googlegr oups.com...
> Can somebody help? I'm simply trying to do the built in tutorials. I
> have 2005 Dev. Edition installed on my PC, and I've reached this step
> in the tutorial process...(Registering, Enabling, and Starting the
> Instance)
> http://msdn2.microsoft.com/en-us/library/ms170375.aspx
> The service will not start, and when I check the event logs, I have
> this error....
> The Notification Services instance encountered an error in one of its
> components and must stop.
> EventParameters:
> Instance Name: Tutorial
> Problem Description: The database was created with or upgraded to a
> different edition of Notification Services. Use the Notification
> Services edition that the database expects.
> Notification Services Edition: Developer Edition
> Database Edition: Standard Edition
> I'm not really comprehending why the database edition says Standard,
> and why my NS edition is Developer, when I installed Dev. Edition on my
> PC.
>
|||OK - got it figured out (I think). Originally, after the launch events
where they gave everyone a free Standard Edition copy, I had installed
the Standard Edition on my PC. Then, after we got the Developer
Edition at work, I either installed some stuff on top of Standard
Edition, or uninstalled and reinstalled Dev. Edition (can't remember
which I did). But clearly there were some components of each edition
lingering around. I uninstalled everything from my machine and
reinstalled on Dev. Edition, and now I'm up and running.
Diane Larsen [MSFT] wrote:[vbcol=seagreen]
> My first suggestion is to make sure you provided the correct SQL Server
> instance name when you created the new instance and when you registered the
> instance. If those are correct, can you run the following query and let me
> know what it reports back?
> SELECT @.@.VERSION;
> On my Dev Edition instance, here's what it returns:
> Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
> Oct 14 2005 00:33:37
> Copyright (c) 1988-2005 Microsoft Corporation
> Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
> --
> Diane Larsen [MSFT]
> Technical Writer
> SQL Server User Education
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1133962720.896823.230160@.g44g2000cwa.googlegr oups.com...

2005 Internal Functions

There are several instances where I've seen this sort of thing:
%%ColumnEx(ObjectID = @.object_id, Name =
@.identity_column).SetIdentityNotForRepl(Value = @.value).
Anyone know what the full syntax is, where they are listed, if we can take
advantage of them etc?
TIA
Those are, as you noted, internal functions -- they are undocumented and
available only within the scope of the resource database. I haven't seen
anything yet, but I'm sure at some point someone will figure out how to hack
SQL Server to get a full list of them -- it's only a matter of time
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"JB" <JB@.Acme.Com> wrote in message
news:OujsIr2IGHA.3452@.TK2MSFTNGP12.phx.gbl...
> There are several instances where I've seen this sort of thing:
> %%ColumnEx(ObjectID = @.object_id, Name =
> @.identity_column).SetIdentityNotForRepl(Value = @.value).
> Anyone know what the full syntax is, where they are listed, if we can take
> advantage of them etc?
> TIA
>
|||I think that the proc sp_identitycolumnforreplication will do what you are
looking for. These system calls are only executable by system processes. You
may be able to find references to them in the system stored procedures and
functions. I don't know how to call them.
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
"JB" <JB@.Acme.Com> wrote in message
news:OujsIr2IGHA.3452@.TK2MSFTNGP12.phx.gbl...
> There are several instances where I've seen this sort of thing:
> %%ColumnEx(ObjectID = @.object_id, Name =
> @.identity_column).SetIdentityNotForRepl(Value = @.value).
> Anyone know what the full syntax is, where they are listed, if we can take
> advantage of them etc?
> TIA
>
|||And even if someone does, they are undocumented for a reason. There is no
guarantee they will function the same from one hotfix to another let alone
one service pack to another. What are you trying to do?
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uM7vn42IGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Those are, as you noted, internal functions -- they are undocumented and
> available only within the scope of the resource database. I haven't seen
> anything yet, but I'm sure at some point someone will figure out how to
> hack SQL Server to get a full list of them -- it's only a matter of time
>
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "JB" <JB@.Acme.Com> wrote in message
> news:OujsIr2IGHA.3452@.TK2MSFTNGP12.phx.gbl...
>

2005 DB backwards-compatible to 2000?

I've attached an SQL2000 db to an SQL2005 Std installation and am curious if
that process can go the other way. Can you detach from 2005 and attach to
2000 without any issues?
Earl wrote:
> Can you detach from 2005 and attach to
> 2000 without any issues?
No. One cannot attach a higher version database to a lower version SQL
Server. The 2005 file format is incompatible with SQL Server 2000.
If you have to migrate backwards, use SSIS to copy the objects and data from
SQL Server 2005 to SQL Server 2000.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200612/1
|||No, You can not do that because of architectural changes.
Thanks
Hari
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23ye9a5kHHHA.3540@.TK2MSFTNGP02.phx.gbl...
> I've attached an SQL2000 db to an SQL2005 Std installation and am curious
> if that process can go the other way. Can you detach from 2005 and attach
> to 200use of 0 without any issues?
>
|||Ahhh, thanks Chris and Hari. Tis' the answer I need.
"Chris O'C via droptable.com" <u29189@.uwe> wrote in message
news:6aacc6ece6f0b@.uwe...
> Earl wrote:
> No. One cannot attach a higher version database to a lower version SQL
> Server. The 2005 file format is incompatible with SQL Server 2000.
> If you have to migrate backwards, use SSIS to copy the objects and data
> from
> SQL Server 2005 to SQL Server 2000.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200612/1
>

2005 beta Backup problems

Hi there,
I've created a maintenance plan in SQL2005 bet 2 (not default instance)
scheduled to run everyday but it never runs, I've all the services running.
When I choose to execute in the moment it says it had success after 5 seconds
but nothing happens really.
Is this a limitation of the beta release? Or am I doing something wrong?
I've another instance instaled in the same server (but with sql2000
version-default instance) and the maintenance plans work fine.
thanks in advance.
Nelson
Please re-post this on the beta newsgroups.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Nelson Andr" <NelsonAndr@.discussions.microsoft.com> wrote in message
news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
> Hi there,
> I've created a maintenance plan in SQL2005 bet 2 (not default instance)
> scheduled to run everyday but it never runs, I've all the services
> running.
> When I choose to execute in the moment it says it had success after 5
> seconds
> but nothing happens really.
> Is this a limitation of the beta release? Or am I doing something wrong?
> I've another instance instaled in the same server (but with sql2000
> version-default instance) and the maintenance plans work fine.
> thanks in advance.
> Nelson
>
|||Where can I find it? I've searched the whole microsoft site and couldn't find
any reference to a sql2005 beta newsgroup
"Wayne Snyder" wrote:

> Please re-post this on the beta newsgroups.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Nelson André" <NelsonAndr@.discussions.microsoft.com> wrote in message
> news:E0B1B330-9D78-4704-8FDF-3A855A5DF64E@.microsoft.com...
>
>
|||http://communities.microsoft.com/new...=sqlserver2005
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nelson André" wrote:
[vbcol=seagreen]
> Where can I find it? I've searched the whole microsoft site and couldn't find
> any reference to a sql2005 beta newsgroup
> "Wayne Snyder" wrote: