Best Replication Method to Use

We want to allow our customer base to be able to access their account
information online. I want to setup a second SQL server so the customers can
use this for looks up. The front end to access this info is web based.
What replication method is the best one to use to update the database say
every 24 hours at night? Thanks!!
if your database is not too large, a snapshot replication maybe best for
you.
else somekind of logshipping will be good too, see the other thread on
simple log shipping.
justin
Scopus69 wrote:
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||I think transactional replication would work for this. However this will
require each table you are replicating to have a primary key.
I am a little confused by the data flow. Are you saying data moves from the
web server SQL Server database to another SQL Server? Or is it moving
internally to the SQL Server supporting the web site.
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
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers
> can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||Sorry for the confusion. The GUI interface to the data is a web interface
that connects to the backend SQL server. What I would like to do is setup
another web & SQL server for our cutomers so they can use it for lookups. I
really don't want them in our prduction DB.
I was wondering what is the best way to get the data off the production SQL
server to the customer SQL server on a nightly basis? I don't think log
shipping will work because it will put the shipped DB in "read only"
So what method would be the best to use? Thanks!
"Hilary Cotter" wrote:

> I think transactional replication would work for this. However this will
> require each table you are replicating to have a primary key.
> I am a little confused by the data flow. Are you saying data moves from the
> web server SQL Server database to another SQL Server? Or is it moving
> internally to the SQL Server supporting the web site.
> --
> 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
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
>
>
|||I think transactional is your best bet.
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
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...[vbcol=seagreen]
> Sorry for the confusion. The GUI interface to the data is a web interface
> that connects to the backend SQL server. What I would like to do is
> setup
> another web & SQL server for our cutomers so they can use it for lookups.
> I
> really don't want them in our prduction DB.
> I was wondering what is the best way to get the data off the production
> SQL
> server to the customer SQL server on a nightly basis? I don't think log
> shipping will work because it will put the shipped DB in "read only"
> So what method would be the best to use? Thanks!
> "Hilary Cotter" wrote:
|||I also like Transactional Replication if the data is dynamic at the source
and the users who will be talking to your target server need updated
information as well for their lookups. If current data is not an issue, that
is they don't mind the data being static, then may be snapshot will work.
But then again it depends on how large the data is. For me one way
Transactional seems to fit the bill here.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enpFYKCDGHA.1028@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think transactional is your best bet.
> --
> 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
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...
interface[vbcol=seagreen]
lookups.[vbcol=seagreen]
will[vbcol=seagreen]
based.
>
sql

Best practice

i have two databases on two different servers, one which is a live server.
What i want to do is run a query to update a table in the second server wit
h
records from the first database. would this be a select if not exists ?It can be an insert followed by a subquery which is based on a NOT exists, a
ssuming that you want to
add the rows that doesn't exist (based on some key column).
Or, it can be an update based on a JOIN (or an update with a number of corre
lated subqueries in SET,
as well as an EXISTS), if you want to update rows that already exists in the
other table, picking
column values from that other table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:B13DDBFC-4697-4DA6-B9C0-5F7A575BC8DF@.microsoft.com...
>i have two databases on two different servers, one which is a live server.
> What i want to do is run a query to update a table in the second server w
ith
> records from the first database. would this be a select if not exists ?
>

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?
Hi
[url]http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1 [/url]
--administaiting best practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?Hi
http://vyaskn.tripod.com/ sql_serve...r />
.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Best Practice

Can someone tell me what the best practice for managing a sql environment
is? Is it best to have a second user account besides your everyday user
account that has elevated permissions required to manage sql?Hi
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
http://vyaskn.tripod.com/sql_server_security_best_practices.htm --security
best practices
"Bad Beagle" <maxwelli@.nospam.postalias> wrote in message
news:OnorAbVIIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Can someone tell me what the best practice for managing a sql environment
> is? Is it best to have a second user account besides your everyday user
> account that has elevated permissions required to manage sql?
>

Benckmark. Inserting records.

How may inserts can SQL execute in a second?
The table that's inserting into has 3 fields (numeric, char(15) and
datetime)
and no other kind of SQL statements are running against it.
Hardware: quad proc, 2GB RAM, RAID.
TIA,
Nicthis is one of those 'it depends' issues...
depedning on the speed of your disks, number of indexes, other users on the
system, blocking, etc...
you could easily do hundreds or a few thousands per second on high end
hardware.
On 2 procs... I'd be thinking more in the range of hundreds... but only
testing will know for sure.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"BN" <nc@.abc.com> wrote in message
news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> How may inserts can SQL execute in a second?
> The table that's inserting into has 3 fields (numeric, char(15) and
> datetime)
> and no other kind of SQL statements are running against it.
> Hardware: quad proc, 2GB RAM, RAID.
> TIA,
> Nic
>|||First, paralellism is the key to optimizing data loading performance.
Create your insert routine so that it can easily be partitioned and
"paralellized".
Depending on your application, you might also consider using bulk insert,
bcp, or DTS - it should be possible to achieve 10's of 1000's of rows per
second with a table that narrow. I would estimate with a midrange server
you could easily go 50K/sec with bulk insert into an empty heap with only a
couple streams.
----
The views expressed here are my own
and not of my employer.
----
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:esoc4fcXDHA.3924@.tk2msftngp13.phx.gbl...
> this is one of those 'it depends' issues...
> depedning on the speed of your disks, number of indexes, other users on
the
> system, blocking, etc...
> you could easily do hundreds or a few thousands per second on high end
> hardware.
> On 2 procs... I'd be thinking more in the range of hundreds... but only
> testing will know for sure.
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "BN" <nc@.abc.com> wrote in message
> news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > How may inserts can SQL execute in a second?
> >
> > The table that's inserting into has 3 fields (numeric, char(15) and
> > datetime)
> > and no other kind of SQL statements are running against it.
> >
> > Hardware: quad proc, 2GB RAM, RAID.
> >
> > TIA,
> >
> > Nic
> >
> >
>|||on a 2x2.4, using individual stored proc calls per single
line insert, i can get > 7K/sec using 10 separate threads
by consolidating more than 1 single row insert statement
into each stored procedure, >18k single row inserts/sec is
possible,
>30k rows/sec on multi-row inserts,
if you are a doing more than one single row insert in a
single stored proc., try using BEGIN/COMMIT TRAN even if
it is not required, this consolidates the transaction log
writes
go to the next sql server magazine connections conference
for more info, brian is there as well
www.sqlconnections.com
>--Original Message--
>How may inserts can SQL execute in a second?
>The table that's inserting into has 3 fields (numeric,
char(15) and
>datetime)
>and no other kind of SQL statements are running against
it.
>Hardware: quad proc, 2GB RAM, RAID.
>TIA,
>Nic
>
>.
>

Beginner need help ... with triger?

I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database whenever the data is inserted or updated. I assume the way to do that is thru the use of a trigger, but have no clue how to begin.

For example:

db #1 Table User has fields;
id, name, address, zip, password, date

db #2 table n_user has fields;
id, name, password

Hope thats enough information to get some assistance...use [Your active DB]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([id], [name], [password])
select [id], [name], [password]
from inserted
end|||Thanks for you help.
I made the trigger but get this error on update of one field value.

Cannot insert explicit value for identity column in table 'FORUM_MEMBERS' when IDENTITY_INSERT is set to OFF.

Any clue??|||Difference between history and active table is difference between picture and movie.
You cannot use the same PK in both tables.

In [Your history DB].dbo.[n_user] table:
1,Add [oldid] column with datatype of [user].[id] column, PK will be on [n_user].[id] column.
2,Also consider using larger datatype on [n_user].[id] column, if [user] table is modified frequently.

3,Modify trigger:

use [Your active DB]
GO
alter trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
delete [Your history DB].dbo.[n_user]
declare @.x int
update dbo.[user] set @.x=1

4...This will help sometimes
Adding FingerPrint timestamp column NOT NULL
Adding CreatedDate datetime column NOT NULL with default getdate().

5,Securing history table:

use [Your history DB]
GO
Deny insert,update,delete,references on dbo.[n_user] to public|||I see parts in the code for insert and delete... what about update?

Again... Thank you very much for your help.|||/*
You wrote:
"I have a table that records data and I want to be able to copy a few of the feilds to another table in a second database
WHENEVER THE DATA IS INSERTED OR UPDATED."
So you did not specify DELETED info.
*/

/* COMMENTED CODE */
--Switching to your active DB
use [Your active DB]
GO
--Creates trigger FOR INSERT,UPDATE on dbo.[user]
--This trigger uses INSERTED table of NEW VALUES for BOTH INSERT AND UPDATE
--( Look at "inserted tables" topic in BOL )
if object_id('TR_User(,I,U,)') is not null drop trigger [TR_User(,I,U,)]
GO
create trigger [TR_User(,I,U,)]
on dbo.[user]
for insert,update
as begin
insert [Your history DB].dbo.[n_user] ([oldid], [name], [password])
select [id], [name], [password]
from inserted
end
GO
--Deleting test rows in HISTORY
delete [Your history DB].dbo.[n_user]
--Filling history table with previosly inserted data (prehistoric)
declare @.x int
update dbo.[user] set @.x=1|||Ok... looks like the insert new member fires off the trigger just fine. Both tables are updated correctly. :)

However, when a user tries to update their profile... They recieve an sql error stating a primary key violation.

Here is the exact trigger that causes that error.

==============

CREATE trigger [TR_Players(,I,U,)]
on dbo.[players]
for insert,update
as begin
insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Now I went ahead and edited the trigger to be this,

=============

CREATE trigger [TR_Players(,I,)]
on dbo.[players]
for Insert
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

As you might expect... this fires off correctly and both tables get their new information.

Now the question is how do I get the update trigger to update table 2 (FORUM_MEMBERS) without attempting to add another row with the same information, thus violating the pk constraint.

I tried to add a second trigger to the same table just for updates like such.

=============

CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
Insert [Forum].dbo.[FORUM_MEMBERS] (M_name, M_username, M_password, M_email, M_quote)
select [PEmail], [PEmail], [Ppassword], [PEmail], [pcomments]
from inserted
end

=============

Too bad that gave me the exact same error. Also, syntax wise... im kind of confused why I had to use "inserted" and not "updated" to get successful syntax checking? Does the temp table updated not exist with triggers? Can I not run two seperate triggers against the same table?

Oh and as an FYI. I did not want delete syntax... I was just commenting that in your post i saw the keyword delete?

Like always... thanks very much for your time to deal with my issues..|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545|||CREATE trigger [TR_Players(,U,)]
on dbo.[players]
for Update
as begin
update fm set
fm.M_name = i.[PEmail]
, fm.M_username = i.[PEmail]
, fm.M_password = i.[Ppassword]
, fm.M_email = i.[PEmail]
, fm.M_quote = i.[pcomments]
from [Forum].dbo.[FORUM_MEMBERS] fm
join inserted i
on fm.M_name=i.[PEmail] --PK join - verify
end

--for more See http://dbforums.com/showthread.php?threadid=640545|||You are like a God to me! Works great. Thank You Thank you! :)|||It seems that I need to make another trigger to pass changed passwords back to the original table. I attempted to modify the previous trigger to work in reverse. But no suck luck. I could use alittle assistance here.

I want the trigger to take the M_password field from the forum_members table (when its updated) and update the other database
table players with the new password. Here is what I came up with...

---------------
CREATE trigger [TR_pw(,U,)]
on [Forum].dbo.[FORUM_MEMBERS]
for Update
as begin
update pl set
pl.Ppassword = i.[M_password]
from dbo.[players] pl
join inserted i
on pl.pemail=i.m_name --PK Join - verify
end
---------------

Thanks.|||Your triggers are probably chaining, learn more about nesting
http://dbforums.com/showthread.php?threadid=640545|||you know your prolly correct... cuz the error message im recieving says something about exceeding the number of database connections.

Ive read the post you refered me to... but am still not sure what it said. Sorry... im new to this. Can I assume that my trigger is formed correctly? But I need to some how limit its ability to fire from an update by another trigger?|||Stike that...
I went to BOL and found how to turn off recursive triggers... I turned them off and it looks like its working. Is there any effect on regular system operation due to this trigger setting?

Thanks for your help.|||You can put "if trigger_nestlevel(@.@.procid)>1 return" in the beginning of your data modifying trigger. Checking-only triggers stay active in the trigger chain. Also even with switching nested triggers off I cannot make Ver1 (table mirroring) working. Also when you are using recursive algorithm for trigger, you must have recursive triggers on.
In most cases you do not need it.

--Ver1
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
insert B select * from inserted
GO
create trigger tiB on B for insert as
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

--Ver2
create table A(X int)
create table B(X int)
GO
create trigger tiA on A for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert B select * from inserted
GO
create trigger tiB on B for insert as
if trigger_nestlevel(@.@.procid)>1 return
insert A select * from inserted
GO
insert A values (1)
GO
drop table A
drop table B

Good luck !

Before Delete

I have 2 databases "Law","Rules" .. the second have tables which is linked
to the first one... so i want to deny Deleting of Record from first if it
has a child record in the other database...
I notice that there is no "Before Delete" trigger in sql server so how could
i control deleteing records from first database..
Second.. how could i roll-back Delete or update operation?
Did you consider a Foreign key Constraint for that ? If it is not
applicable you can do a ROLLBACK within a trigger and raise an error to
show up the error to the user.
http://groups.google.de/group/micros...18307e92ac868c
HTH, Jens Suessmeyer.
|||Instead of trying ot use a trigger, how about applying a foreign key
constraint instead? Then when you try to delete a row from the first
table you'll get an error if there's a dependent row in the second
table. It'll also be much faster than using a trigger.
On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
wrote:

>I have 2 databases "Law","Rules" .. the second have tables which is linked
>to the first one... so i want to deny Deleting of Record from first if it
>has a child record in the other database...
>I notice that there is no "Before Delete" trigger in sql server so how could
>i control deleteing records from first database..
>Second.. how could i roll-back Delete or update operation?
>
|||hi,
bradsbulkmail@.comcast.net wrote:[vbcol=seagreen]
> Instead of trying ot use a trigger, how about applying a foreign key
> constraint instead? Then when you try to delete a row from the first
> table you'll get an error if there's a dependent row in the second
> table. It'll also be much faster than using a trigger.
> On Sat, 8 Oct 2005 16:16:51 +0200, "Islamegy" <Islamegy@.Private.4me>
> wrote:
have you tried something like
SET NOCOUNT ON
CREATE DATABASE a
CREATE DATABASE b
GO
USE a
CREATE TABLE dbo.m (
Id int NOT NULL PRIMARY KEY ,
Descr varchar (10) NOT NULL
)
GO
USE b
GO
CREATE TABLE dbo.d (
ID int NOT NULL PRIMARY KEY ,
IdRif int NOT NULL
CONSTRAINT fk_d_m FOREIGN KEY
REFERENCES a.dbo.m (Id) ,
Descr varchar (10) NOT NULL
)
GO
USE master
GO
DROP DATABASE a
DROP DATABASE b
?
the actual result is
Server: Msg 1763, Level 16, State 1, Line 1
Cross-database foreign key references are not supported. Foreign key
'a.dbo.m'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
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