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)
>
>

2005 Express

It's time to learn more about 2005. To start, I can't find SQLagent anywhere
in my SQLExpress install. I see docs that say to look under managment in
MS's Object Explorer, but I don't see anything under there but the logs and
the Activity Monitor.
Nor do I see anything thing specific on the Microsoft download page. I
installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
Server Management Studio Express", but not advanced services.
JayThere is no SQL Server Agent in Express Edition.
For more information SQL Server 2005 Express Edition, you may want to see
the following document:
http://msdn2.microsoft.com/en-us/library/ms345154.aspx
--
Ekrem Önsoy
"Jay" <spam@.nospam.org> wrote in message
news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
> It's time to learn more about 2005. To start, I can't find SQLagent
> anywhere in my SQLExpress install. I see docs that say to look under
> managment in MS's Object Explorer, but I don't see anything under there
> but the logs and the Activity Monitor.
> Nor do I see anything thing specific on the Microsoft download page. I
> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
> Server Management Studio Express", but not advanced services.
> Jay
>|||"Jay" <spam@.nospam.org> wrote in message
news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
> It's time to learn more about 2005. To start, I can't find SQLagent
> anywhere in my SQLExpress install. I see docs that say to look under
> managment in MS's Object Explorer, but I don't see anything under there
> but the logs and the Activity Monitor.
> Nor do I see anything thing specific on the Microsoft download page. I
> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
> Server Management Studio Express", but not advanced services.
> Jay
>
Agent is not included with Express.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
--
David Portas|||Jay,
SQL Agent does not come with SQL Server Express. You can use the Windows
Scheduler and SQLCMD to run SQL Jobs according to schedule. And of course,
people are writing code to fill the hole. For $79 the following solution is
being offered: http://www.valesoftware.com/products-express-agent.php
RLF
"Jay" <spam@.nospam.org> wrote in message
news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
> It's time to learn more about 2005. To start, I can't find SQLagent
> anywhere in my SQLExpress install. I see docs that say to look under
> managment in MS's Object Explorer, but I don't see anything under there
> but the logs and the Activity Monitor.
> Nor do I see anything thing specific on the Microsoft download page. I
> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
> Server Management Studio Express", but not advanced services.
> Jay
>|||OK, this explains why I kept finding references and was going nuts trying to
figure out how to get/enable it.
I assume that in the full versions, it works pretty much just like it does
in 2000 EM?
I also assume that to see it in MS (connected to a 2005 instance), you have
to be a admin on that machine.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:2D8BFE09-BCC2-46F2-BDB2-36E7EB1971DE@.microsoft.com...
> There is no SQL Server Agent in Express Edition.
> For more information SQL Server 2005 Express Edition, you may want to see
> the following document:
> http://msdn2.microsoft.com/en-us/library/ms345154.aspx
> --
> Ekrem Önsoy
>
>
> "Jay" <spam@.nospam.org> wrote in message
> news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> It's time to learn more about 2005. To start, I can't find SQLagent
>> anywhere in my SQLExpress install. I see docs that say to look under
>> managment in MS's Object Explorer, but I don't see anything under there
>> but the logs and the Activity Monitor.
>> Nor do I see anything thing specific on the Microsoft download page. I
>> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
>> Server Management Studio Express", but not advanced services.
>> Jay
>|||Jay,
You don't need to be an admin to see SQL Agent on a non-Express SQL Server
2005, but you do need some rights. You can look up msdb fixed database
roles:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
Rights increase as you proceed down the list.
RLF
"Jay" <nospan@.nospam.org> wrote in message
news:%23wOohW0DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> OK, this explains why I kept finding references and was going nuts trying
> to figure out how to get/enable it.
> I assume that in the full versions, it works pretty much just like it does
> in 2000 EM?
> I also assume that to see it in MS (connected to a 2005 instance), you
> have to be a admin on that machine.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:2D8BFE09-BCC2-46F2-BDB2-36E7EB1971DE@.microsoft.com...
>> There is no SQL Server Agent in Express Edition.
>> For more information SQL Server 2005 Express Edition, you may want to see
>> the following document:
>> http://msdn2.microsoft.com/en-us/library/ms345154.aspx
>> --
>> Ekrem Önsoy
>>
>>
>> "Jay" <spam@.nospam.org> wrote in message
>> news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> It's time to learn more about 2005. To start, I can't find SQLagent
>> anywhere in my SQLExpress install. I see docs that say to look under
>> managment in MS's Object Explorer, but I don't see anything under there
>> but the logs and the Activity Monitor.
>> Nor do I see anything thing specific on the Microsoft download page. I
>> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
>> Server Management Studio Express", but not advanced services.
>> Jay
>>
>|||Thanks, Russell.
While what you say sounds reasonable, what I am seeing doesn't match.
I connected to our 2005 Standard Edition server using my rather limited
account and I do not see "Maintenance Plans" under the Managment tree.
However, when I checked it with the guy doing admin on that box, he does
have "Maintenance Plans" listed.
I supose this doesn't really matter, as I'll have full assess on that
machine shortly. What I don't (and need to know) is how to use it. I'm
hoping that it is a simple as 2000 SQLagent.
Jay
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23gu7y10DIHA.3332@.TK2MSFTNGP04.phx.gbl...
> Jay,
> You don't need to be an admin to see SQL Agent on a non-Express SQL Server
> 2005, but you do need some rights. You can look up msdb fixed database
> roles:
> SQLAgentUserRole
> SQLAgentReaderRole
> SQLAgentOperatorRole
> Rights increase as you proceed down the list.
> RLF
> "Jay" <nospan@.nospam.org> wrote in message
> news:%23wOohW0DIHA.4772@.TK2MSFTNGP02.phx.gbl...
>> OK, this explains why I kept finding references and was going nuts trying
>> to figure out how to get/enable it.
>> I assume that in the full versions, it works pretty much just like it
>> does in 2000 EM?
>> I also assume that to see it in MS (connected to a 2005 instance), you
>> have to be a admin on that machine.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:2D8BFE09-BCC2-46F2-BDB2-36E7EB1971DE@.microsoft.com...
>> There is no SQL Server Agent in Express Edition.
>> For more information SQL Server 2005 Express Edition, you may want to
>> see the following document:
>> http://msdn2.microsoft.com/en-us/library/ms345154.aspx
>> --
>> Ekrem Önsoy
>>
>>
>> "Jay" <spam@.nospam.org> wrote in message
>> news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> It's time to learn more about 2005. To start, I can't find SQLagent
>> anywhere in my SQLExpress install. I see docs that say to look under
>> managment in MS's Object Explorer, but I don't see anything under there
>> but the logs and the Activity Monitor.
>> Nor do I see anything thing specific on the Microsoft download page. I
>> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
>> Server Management Studio Express", but not advanced services.
>> Jay
>>
>>
>|||Hi Jay,
Today I realized that, if you do not have sysadmin server fixed role,
Maintanence Tasks node does not appear under Management in SSMS.
It's quite easy to use it. Don't worry.
Maybe the following link helps for a start.
http://msdn2.microsoft.com/en-us/library/ms189953.aspx
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Jay" <nospan@.nospam.org> wrote in message
news:%23Mpse$0DIHA.536@.TK2MSFTNGP06.phx.gbl...
> Thanks, Russell.
> While what you say sounds reasonable, what I am seeing doesn't match.
> I connected to our 2005 Standard Edition server using my rather limited
> account and I do not see "Maintenance Plans" under the Managment tree.
> However, when I checked it with the guy doing admin on that box, he does
> have "Maintenance Plans" listed.
> I supose this doesn't really matter, as I'll have full assess on that
> machine shortly. What I don't (and need to know) is how to use it. I'm
> hoping that it is a simple as 2000 SQLagent.
> Jay
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23gu7y10DIHA.3332@.TK2MSFTNGP04.phx.gbl...
>> Jay,
>> You don't need to be an admin to see SQL Agent on a non-Express SQL
>> Server 2005, but you do need some rights. You can look up msdb fixed
>> database roles:
>> SQLAgentUserRole
>> SQLAgentReaderRole
>> SQLAgentOperatorRole
>> Rights increase as you proceed down the list.
>> RLF
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:%23wOohW0DIHA.4772@.TK2MSFTNGP02.phx.gbl...
>> OK, this explains why I kept finding references and was going nuts
>> trying to figure out how to get/enable it.
>> I assume that in the full versions, it works pretty much just like it
>> does in 2000 EM?
>> I also assume that to see it in MS (connected to a 2005 instance), you
>> have to be a admin on that machine.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:2D8BFE09-BCC2-46F2-BDB2-36E7EB1971DE@.microsoft.com...
>> There is no SQL Server Agent in Express Edition.
>> For more information SQL Server 2005 Express Edition, you may want to
>> see the following document:
>> http://msdn2.microsoft.com/en-us/library/ms345154.aspx
>> --
>> Ekrem Önsoy
>>
>>
>> "Jay" <spam@.nospam.org> wrote in message
>> news:ePp0TNzDIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> It's time to learn more about 2005. To start, I can't find SQLagent
>> anywhere in my SQLExpress install. I see docs that say to look under
>> managment in MS's Object Explorer, but I don't see anything under
>> there but the logs and the Activity Monitor.
>> Nor do I see anything thing specific on the Microsoft download page. I
>> installed Install "Microsoft SQL Server 2005 Express Edition" and "SQL
>> Server Management Studio Express", but not advanced services.
>> Jay
>>
>>
>>
>