Do I need stop Agents during primary Server power down?

Hi,
Does anyone can tell that our production primary site need perform server
power down, and I have the databases transactional replication with push
setup on primary site? The secondary site will run all the time. Before,
primary site perform the power down, do I need stop the Log Reader and
Distribution Agents? What if I am not stopping those agents?
Regards,
Chen
You will be fine. Transactional replication replicates transactionally. So
if you power off in the middle of something, when it starts up again, it
will pick up where it left off.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:D91896AE-5BF7-47FA-A0D7-D4BFC0CAF422@.microsoft.com...
> Hi,
> Does anyone can tell that our production primary site need perform server
> power down, and I have the databases transactional replication with push
> setup on primary site? The secondary site will run all the time. Before,
> primary site perform the power down, do I need stop the Log Reader and
> Distribution Agents? What if I am not stopping those agents?
> Regards,
> Chen
>
sql

Do I need second license?

Hello,
I have MS SQL Server 2005 Standard Edition with one Client Access License.
May I
install the server as Primary on one machine and as Secondary (backup)
with Log Shipping on it on another machine, using same license?
Thanks,
GBYes,
But, note, its there for backup only and should not be used to run queries
etc...
So, if you ship the logs across to the backup server and then use database
snapshots then you are probably breaking the liecence terms and need another
liecence.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"GB" <gennady@.telus.net> wrote in message
news:79SJf.7479$n67.3175@.edtnps89...
> Hello,
> I have MS SQL Server 2005 Standard Edition with one Client Access License.
> May I
> install the server as Primary on one machine and as Secondary (backup)
> with Log Shipping on it on another machine, using same license?
> Thanks,
> GB
>|||Since GB really asked two questions (one in the subject and one in the body)
in which a YES can answer both but it has opposite meaning I wanted to say:
No you don't need two licenses<g>.
Andrew J. Kelly SQL MVP
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:O0phfIUNGHA.2472@.TK2MSFTNGP11.phx.gbl...
> Yes,
> But, note, its there for backup only and should not be used to run queries
> etc...
> So, if you ship the logs across to the backup server and then use database
> snapshots then you are probably breaking the liecence terms and need
> another liecence.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "GB" <gennady@.telus.net> wrote in message
> news:79SJf.7479$n67.3175@.edtnps89...
>|||Lol - teach me for reading the Q and ignoring the subject - doh!
Absolutely Andrew, and thanks for waking me up!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e7XMoyVNGHA.536@.TK2MSFTNGP09.phx.gbl...
> Since GB really asked two questions (one in the subject and one in the
> body) in which a YES can answer both but it has opposite meaning I wanted
> to say: No you don't need two licenses<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:O0phfIUNGHA.2472@.TK2MSFTNGP11.phx.gbl...
>

Do i need primary key?

Here is the scenario
I have a table (let's call it tblTest) with 3 fields, UserID, Note,
NoteEnterDate (simple one to many relationship. user can have many notes)
Query would be to join it with user table and get the first and last name of
the user (based on UserID) and show the Note and NoteEnterDate (at this
point, I cannot think of any other query that uses this tblTest other than
this).
As far as index is concerned, I think I just need to create clustered index
on UserID.
I know it is good to declare a primary key for every table. Is there a need
for primary key for tblTest perhaps by adding another field called NoteID?
Thanks"Justin" <jus820@.hotmail.com> wrote in message
news:e4OGN5TbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
> of the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered
> index on UserID.
> I know it is good to declare a primary key for every table. Is there a
> need for primary key for tblTest perhaps by adding another field called
> NoteID?
>
Would you allow the same (UserID,Note,NoteEnterDate) to be entered twice?
If so, add a synthetic NoteID to tell them apart. If not, create the PK on
(UserID, NoteEnterDate,Note).
David|||you don 't need to waste the space on another column, if the noteid is not
having significance. and if you are going to use an identity for it then its
of no use at all.
u can use user ID and NoteEnterDate as the primary key.
Hope this helps.
--
"Justin" wrote:

> Here is the scenario
> I have a table (let's call it tblTest) with 3 fields, UserID, Note,
> NoteEnterDate (simple one to many relationship. user can have many notes)
> Query would be to join it with user table and get the first and last name
of
> the user (based on UserID) and show the Note and NoteEnterDate (at this
> point, I cannot think of any other query that uses this tblTest other than
> this).
> As far as index is concerned, I think I just need to create clustered inde
x
> on UserID.
> I know it is good to declare a primary key for every table. Is there a ne
ed
> for primary key for tblTest perhaps by adding another field called NoteID?
> Thanks
>
>|||NoteEnterDate is smalldatetime datatype (since we don't basically care about
the seconds). User can type more than one note in a minute. If I create
primary key on UserID and NoteEnterDate, this is not allowed.
Even even if this was not allowed (creating more than one notes in a
minute), what would be the point of creating primary key on UserID,
NoteEnterDate?
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:0F05A821-5FFD-4A20-97E2-301565FFA187@.microsoft.com...
> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
> u can use user ID and NoteEnterDate as the primary key.
> Hope this helps.
> --
>
>
> "Justin" wrote:
>|||> Even even if this was not allowed (creating more than one notes in a
> minute), what would be the point of creating primary key on UserID,
> NoteEnterDate?
Maybe to prevent someone hitting refresh on your web page, 80 times in a
minute, and populating your table with redundant data.
A|||> you don 't need to waste the space on another column, if the noteid is not
> having significance. and if you are going to use an identity for it then
> its
> of no use at all.
I don't think I particularly agree. What harm does an IDENTITY column do?
I don't think it's as horrible as you make it out to be. The OP doesn't
know all future requirements now, so we can't really gauge its significance,
but let's say you wanted to track notes for some reason (e.g. show who has
viewed them and when). You might create another table called:
CREATE TABLE dbo.NoteTracking
(
? FOREIGN KEY REFERENCES dbo.tblTest(?),
ViewDate SMALLDATETIME
)
Surely you don't suggest it would be better to make up this primary key
UserID,NoteEnterDate and use that as your reference in the secondary table?
Like the OP, I'm not clear on what business sense a primary key on
UserID,NoteEnterDate would make, other than to fulfill the mantra "every
table must have a primary key." If it really is possible (and even
desirable) for a single user to enter two notes in one minute, then we're
back at square one.
A|||All tables must have at least one set of columns that can invariably
identify every row in the table. And there are obvious practical reasons to
explicitly declare one of those sets of columns as the primary key.
Anithsql

Do I need a Primary Key?

I am programming a site in ASP. I am used to using Access which forces you
to have a Primary Key. However, I am learning to use SQL which does not
seem to force you to have a Primary Key. Do I really need one? Please let
me know and why. Thanks!!Techniclly speaking you don't need one, but I would consider any table
without a primary key poor design.
The main reason you need one is that the primary key guarantees you'll have
a column in your table that you can use to uniquely identify each record.
Without a primary key you could potentialy end up with multiple identical
records in your table which you're not able to identify individualy using a
select, update or delete statement.
HTH
Karl Gram
http://www.gramonline.com
"michaaal" <res0gyio@.verizon.net> wrote in message
news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> I am programming a site in ASP. I am used to using Access which forces
you
> to have a Primary Key. However, I am learning to use SQL which does not
> seem to force you to have a Primary Key. Do I really need one? Please
let
> me know and why. Thanks!!
>|||Hi,
To add on to old post,
1. Enforce uniqueness for values entered in specified columns
2. Will not allow nulls.
3. If you define a primary key for a table in your database, you can relate
that table to other tables, thus reducing the need for redundant data.
This will allow you to have Parent child relation ship with out writing
code.
4. This will allow you to do Cascading (Refer boks inline)
Always for a better database modelling we should enforce Primary key /
Foregn key concept.
Thanks
Hari
MCDBA
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message
news:#FrwxMkDEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Techniclly speaking you don't need one, but I would consider any table
> without a primary key poor design.
> The main reason you need one is that the primary key guarantees you'll
have
> a column in your table that you can use to uniquely identify each record.
> Without a primary key you could potentialy end up with multiple identical
> records in your table which you're not able to identify individualy using
a
> select, update or delete statement.
> --
> HTH
> Karl Gram
> http://www.gramonline.com
> "michaaal" <res0gyio@.verizon.net> wrote in message
> news:O6$6#0iDEHA.3784@.TK2MSFTNGP10.phx.gbl...
> you
> let
>|||> 3. If you define a primary key for a table in your database, you can
relate
> that table to other tables, thus reducing the need for redundant data.
> This will allow you to have Parent child relation ship with out
writing
> code.
The above statement brings on another question I had... Do I really
WANT to do this type of thing on the SQL server level? Or do I
want to do this type of thing in my code. My first inclination is to do
it in the code, however, I have not really sat down and researched the
possible speed differences. Any comments on this? Thank you!|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
Enforcing constraints in the code means that they will only be enforced in
your code. If someone uses Access or similar to access your database
directly they can by-pass all your constraints and wreak havoc.
It is also (IMHO) easier to document and troubleshoot. The constraints are
there as part of your table definition. All your data-centric info is in
one place.
As for speed, if you have the contsraints in SQL Server the optimizer and
can make informed decisions on how best to optimize the queries. Otherwise
in the code you will have to decide how to join the data, which is either
going to be very complicated, or not the best method in every circumstance.
Finally, and this is more a judgement on my programming skills than yours,
constraints work pretty much the way it says on the box. If you are
hand-coding all this, then bugs can creep in, you may not foresee every
eventuality, etc.
So my vote is for data-centric rules to be in the data tier.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||No, you should always enforce constraints at the DATA level. Enforcing them
in the code means that your data can become corrupt by someone simply
bypassing your application (e.g. running an insert/update/delete from Query
Analyzer).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"michaaal" <res0gyio@.verizon.net> wrote in message
news:eRB$WFlDEHA.3980@.TK2MSFTNGP09.phx.gbl...
> relate
> writing
> The above statement brings on another question I had... Do I really
> WANT to do this type of thing on the SQL server level? Or do I
> want to do this type of thing in my code. My first inclination is to do
> it in the code, however, I have not really sat down and researched the
> possible speed differences. Any comments on this? Thank you!
>
>|||RE/
>No, you should always enforce constraints at the DATA level. Enforcing the
m
>in the code means that your data can become corrupt by someone simply
>bypassing your application (e.g. running an insert/update/delete from Query
>Analyzer).
Do you prefer to enforce RI via triggers or the other way?
"Other way" because I don't know enough go spell it out...Converted a few MS
Access DBs and wound up with triggers - so that's all I know. MSDN Univers
al
coming soon - so I guess I'll get the option to go either way via MS Visio's
DB
design tool...
--
PeteCresswell|||No, triggers can be pretty poor for performance, depending on other
circumstances. I prefer traditional primary/foreign key relationships, then
violations are stopped in their tracks rather than after the fact.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"(Pete Cresswell)" <x@.y.z> wrote in message
news:k2op50heohj4o6j7tkgp05o7gk61hf1eue@.
4ax.com...
> RE/
> Do you prefer to enforce RI via triggers or the other way?
> "Other way" because I don't know enough go spell it out...Converted a few
> MS
> Access DBs and wound up with triggers - so that's all I know. MSDN
> Universal
> coming soon - so I guess I'll get the option to go either way via MS
> Visio's DB
> design tool...
> --
> PeteCresswell

Distribution Agent error

I use SQLServer2K to manage a sortation system. I have
one server that acts as the primary server and a backup
server that subscribes to the publication on the primary.
Lately, my distribution agent has been failing with the
following comment as the Last Action:
"The row was not found at the Subscriber when applying
the replicated command."
The time stamp shows this to occur at the beginning of
the work day. The last command run is
'CALL sp_MSdel_tblData (1920859)' where tblData is one of
the articles in a transactional publication. I also note
the Transaction Log start to pile up.
To clean things up, I wait til the end of the work day
(when replication should be idle) and do the following
procedure in this order:
1) Run the sp_ReplDone stored procedure.
(EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
@.numtrans = 0, @.time = 0, @.reset = 1)
2) Reinitialize subscriptions.
3) Run the snapshot agent.
4) Restart the distribution agent.
5) Backup the Transaction Log file.
6) Shrink the Transaction Log file.
USE Database
GO
DBCC SHRINKFILE (Database_Log, 1)
GO
Everything seems to run fine until the next morning and
the dist agent fails again. Is there something I'm
lacking? or am I doing something out of sequence?
Roger.
When you get this problem it means that the distribution agent is trying to
delete a row on the subscriber that does not exist there.
The first thing you have to do is identify why you have a database
consistency problem between your publisher and subscriber. You should run a
validation to determine how out of sync you are and then figure out why your
subscriber is out of sync. Are there any ohter processes writing to the
subscriber? Any users incorrectly pointing to this database instead of teh
publisher. You might want to run profiler to identify them.
If you like to live dangerously you can use the continue on data consistency
errors profile of the distribution agent, but then you will have no idea how
out of sync your subsciber is, or whether it is getting progressively out of
sync, or getting more in sync.
Your Transaction log should not be "filling up". You should be dumping it to
prevent the log from getting too large. For VLDBs dump every minute. Run the
full recovery model for best results, although you can run the simple
recovery model if you simply don't care about point in time recovery.
run a dbcc opentran to see if there are any open transactions and if you can
kill them.
if there is a value for the oldest non distributed transaction, your log
reader is getting behind or stopped.
While your strategy does work, it is not the best approach for this problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Roger Denison" <anonymous@.discussions.microsoft.com> wrote in message
news:243601c49bfc$47b93340$a601280a@.phx.gbl...
> I use SQLServer2K to manage a sortation system. I have
> one server that acts as the primary server and a backup
> server that subscribes to the publication on the primary.
> Lately, my distribution agent has been failing with the
> following comment as the Last Action:
> "The row was not found at the Subscriber when applying
> the replicated command."
> The time stamp shows this to occur at the beginning of
> the work day. The last command run is
> 'CALL sp_MSdel_tblData (1920859)' where tblData is one of
> the articles in a transactional publication. I also note
> the Transaction Log start to pile up.
> To clean things up, I wait til the end of the work day
> (when replication should be idle) and do the following
> procedure in this order:
> 1) Run the sp_ReplDone stored procedure.
> (EXEC sp_repldone @.xactid = NULL, @.xact_segno = NULL,
> @.numtrans = 0, @.time = 0, @.reset = 1)
> 2) Reinitialize subscriptions.
> 3) Run the snapshot agent.
> 4) Restart the distribution agent.
> 5) Backup the Transaction Log file.
> 6) Shrink the Transaction Log file.
> USE Database
> GO
> DBCC SHRINKFILE (Database_Log, 1)
> GO
> Everything seems to run fine until the next morning and
> the dist agent fails again. Is there something I'm
> lacking? or am I doing something out of sequence?
> Roger.