Binary Data on Multiple Servers

I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.

When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).

--To create my cert and key:

USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO

CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO

To encrypt:

OPEN SYMMETRIC KEY MyKey

DECRYPTION BY CERTIFICATE MyCert

WITH PASSWORD = 'same password as above';

Insert my record, use scope_identity to return primary key into @.CustomerID.

INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @.DataToEncrypt, 1, CONVERT( varbinary, @.CustomerID)))

CLOSE SYMMETRIC KEY MyKey

To decrypt:

SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @.CustomerID))) as PlainTextData

FROM Customers

WHERE (CustomerID= @.CustomerID)

Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.

Thanks in advance for your help!

Did you up the database master key on the server and restore it to your home machine? If not, then the data will not decrypt on your home machine. The database master key is used to encrypt the symmetric key which would then be used to decrypt your data. If you create the "same" symmetric key, but use a different database master key, then the identity will be different which will prevent decryption. This is by design, because it ensures that someone can fake security credentials and gain access to your data.|||

That's one thing I didn't do! Thanks Michael. That's got to be it.

Thank you,

Richard

|||

You generated different keys. Have a look at http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx for how you can regenerate the same key on different machines.

Thanks
Laurentiu

|||

Laurentiu, great article! I didn't realize the Key_Source and Identity_Value were essential to make the key work in multiple databases. I thought as long as the algorithm and name were identical everything would decrypt properly. Once I created a new key like you mentioned in your article, the decryption worked great across both databases. Thanks again!

Richard

sql

Bilateral Transactional Replication Problem

Hi,

I have 4 servers formed with bilateral transactional replication (each server is distributing, publicator and subscriber), the problem is that the transactionals processes to be failing in the application client as soon as the subscriptions is activate.

Please help me!!

dark.otakon@.gmail.com

What kind of errors do you have?

To set up bidirectional replication, you need to run on "loopback detection" at the publication level. And you can not have loops in the replication topology.

If you are using SQL Server 2005, you can consider peer to peer replication.

|||

I am using Sql Server 2000 with SP4, I am creating the subscriptions with the script:

exec sp_addsubscription @.publication = N'TRAFICO', @.article = N'all', @.subscriber = N'DCI-APP', @.destination_db = N'HHSITR', @.sync_type = N'none', @.update_mode = N'read only', @.offloadagent = 0, @.dts_package_location = N'distributor',@.loopback_detection = 'true'

The error is in the application client is "Cannot find the member name "Systema.Data.SqlClient.SqlError.""

Big-IP and SSL

Hi! I am working on some issues related to Reporting Services and SSL, I
wanted to know:
- If I install a NLB cluster and I add "n" servers, how many SSL
Certificates will I need? 1 or "n"?
- does anyone know if there are any issue related to MRS, SSL and the
Big-IP Load Balancer?
Thank you very much in advance :-)
Jose
--
------
Jose Ignacio Rodas, A+, CCEA, MCSEI use BigIP's in production, LVS' in test and development. If I understand
your first question about NLB clusters, you will need one certificate per
domain name. If your NLB cluster, comprised of "n" servers, answers up to
www.mydomain.com then you will need a certificate on www.mydomain.com. I
worked with NLB in 2000 and do not remember certificate management as being a
part of it, I could be wrong, not sure about 2003. As far as the BigIP and
SQL RS, good luck. It will work, if configured properly. Supposedly SP2 of
SQL RS added a feature to support SSL termination prior to the web server if
the proper HTTP headers are passed. I was able to get Reports working, but
not ReportServer. The information is available in the SP2 update
documentation. Hope this helps.
"Jose Ignacio Rodas" wrote:
> Hi! I am working on some issues related to Reporting Services and SSL, I
> wanted to know:
> - If I install a NLB cluster and I add "n" servers, how many SSL
> Certificates will I need? 1 or "n"?
> - does anyone know if there are any issue related to MRS, SSL and the
> Big-IP Load Balancer?
> Thank you very much in advance :-)
> Jose
> --
>
> ------
> Jose Ignacio Rodas, A+, CCEA, MCSE|||http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm#_http_headers
That is the SP2 readme that talks about SSL termination
"Brian" wrote:
> I use BigIP's in production, LVS' in test and development. If I understand
> your first question about NLB clusters, you will need one certificate per
> domain name. If your NLB cluster, comprised of "n" servers, answers up to
> www.mydomain.com then you will need a certificate on www.mydomain.com. I
> worked with NLB in 2000 and do not remember certificate management as being a
> part of it, I could be wrong, not sure about 2003. As far as the BigIP and
> SQL RS, good luck. It will work, if configured properly. Supposedly SP2 of
> SQL RS added a feature to support SSL termination prior to the web server if
> the proper HTTP headers are passed. I was able to get Reports working, but
> not ReportServer. The information is available in the SP2 update
> documentation. Hope this helps.
> "Jose Ignacio Rodas" wrote:
> > Hi! I am working on some issues related to Reporting Services and SSL, I
> > wanted to know:
> >
> > - If I install a NLB cluster and I add "n" servers, how many SSL
> > Certificates will I need? 1 or "n"?
> >
> > - does anyone know if there are any issue related to MRS, SSL and the
> > Big-IP Load Balancer?
> >
> > Thank you very much in advance :-)
> >
> > Jose
> > --
> >
> >
> >
> > ------
> > Jose Ignacio Rodas, A+, CCEA, MCSE

bigint problem

Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
Troy
Values in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>
|||What provider are you using? It may not understand a BigInt datatype.
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>

bigint problem

Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
--
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
--
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>sql

bigint problem

Hi,
I have two sql server enterprise servers running sp4 on windows server2003
sp1
I have a DTS package that does some various processing on one machine, then
at the end of the package. I copy tables to the other sql server box.
In one of the tables being copied, I have a bigint data type. The table
copies and shows that the bigint datatype is still part of the table
definition, but the values in the bigint column are negatives and positives.
Does anyone have any ideas as to what might be causing this?
thanks in advance,
TroyValues in a bigint column can be negative as well as positive, but I assume
that you have only positive values on one side and end up with negative and
positive values on the other? If that's the case it looks to me like your
bigints are accidentally treated as ints somewhere along the way, at a
binary level.
Jacco Schalkwijk
SQL Server MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>|||What provider are you using? It may not understand a BigInt datatype.
Andrew J. Kelly SQL MVP
"Troy Sherrill" <tsherrill@.nc.rr.com> wrote in message
news:ekzUk8kfFHA.460@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have two sql server enterprise servers running sp4 on windows server2003
> sp1
> I have a DTS package that does some various processing on one machine,
> then
> at the end of the package. I copy tables to the other sql server box.
> In one of the tables being copied, I have a bigint data type. The table
> copies and shows that the bigint datatype is still part of the table
> definition, but the values in the bigint column are negatives and
> positives.
> Does anyone have any ideas as to what might be causing this?
> thanks in advance,
> Troy
>

Big query on different servers

Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.
Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:

> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...[vbcol=seagreen]
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
to[vbcol=seagreen]
On eight[vbcol=seagreen]
c0000005[vbcol=seagreen]
Stored[vbcol=seagreen]
plus[vbcol=seagreen]
fails at[vbcol=seagreen]
by[vbcol=seagreen]
same[vbcol=seagreen]
works[vbcol=seagreen]
during[vbcol=seagreen]
|||
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)
sql

Big query on different servers

Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
> > Hi.
> > From my dektop PC I started Query Analyser on 12 servers and used it to
> > execute a Stored Procedure (same database structure on all servers). On eight
> > it worked, on four it did not, giving the message:
> >
> > ODBC: Msg 0, Level 19,. State 1
> > SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Connection broken
> >
> > Can someone tell me what is going on and how to get round this? The Stored
> > Procedure is like this:
> >
> > CREATE TABLE... (no problem here)
> > INSERT INTO... several thousand rows generated by reading a million plus
> > rows
> > from a different database on the same server
> > (this sometimes works, sometimes it fails at
> > this point)
> > UPDATE... all the rows from the INSERT, again with data generated by
> > reading a
> > million plus rows from a different database on the same
> > server
> > (if it gets beyond this point it works
> > correctly)
> > UPDATE... as the first update
> > UPDATE... as the first update
> > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> >
> > A valid run takes about 10-15 minutes. The last one I tried failed during
> > the first UPDATE after 26 seconds.
> >
> > TIA,
> >
> > Peter.|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL version and service pack level (select @.@.version)?
> >
> > Check that you are on the latest SP's and possible hotfixes.
> >
> > Regards
> > Mike
> >
> > "PeterHyssett" wrote:
> >
> > > Hi.
> > > From my dektop PC I started Query Analyser on 12 servers and used it
to
> > > execute a Stored Procedure (same database structure on all servers).
On eight
> > > it worked, on four it did not, giving the message:
> > >
> > > ODBC: Msg 0, Level 19,. State 1
> > > SqlDumpExceptionHandler: Process nnn generated fatal exception
c0000005
> > > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> > >
> > > Connection broken
> > >
> > > Can someone tell me what is going on and how to get round this? The
Stored
> > > Procedure is like this:
> > >
> > > CREATE TABLE... (no problem here)
> > > INSERT INTO... several thousand rows generated by reading a million
plus
> > > rows
> > > from a different database on the same server
> > > (this sometimes works, sometimes it
fails at
> > > this point)
> > > UPDATE... all the rows from the INSERT, again with data generated
by
> > > reading a
> > > million plus rows from a different database on the
same
> > > server
> > > (if it gets beyond this point it
works
> > > correctly)
> > > UPDATE... as the first update
> > > UPDATE... as the first update
> > > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> > >
> > > A valid run takes about 10-15 minutes. The last one I tried failed
during
> > > the first UPDATE after 26 seconds.
> > >
> > > TIA,
> > >
> > > Peter.|||> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)

Bidirectional transactional replication question

Hi,
I'm confused. I have bidirectional transactional
replication configured between 2 servers (sql server 2000
sp3). Same table is publisher and subscriber on both
servers.
Execution of:
sp_helpsubscription <publication name>
shows that loopback_detection is set to "No" on both
servers, and still, table changes are not being replicated
back, to the orginating server.
How is that posible? Is there any other way to prevent
changes to get back to the originating server?
Thanks,
OJ
It should be set to 1 or true for it to work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:0e5701c4a656$16a33710$a401280a@.phx.gbl...
> Hi,
> I'm confused. I have bidirectional transactional
> replication configured between 2 servers (sql server 2000
> sp3). Same table is publisher and subscriber on both
> servers.
> Execution of:
> sp_helpsubscription <publication name>
> shows that loopback_detection is set to "No" on both
> servers, and still, table changes are not being replicated
> back, to the orginating server.
> How is that posible? Is there any other way to prevent
> changes to get back to the originating server?
> Thanks,
> OJ

Bi-Directional Transactional Replication - Problem

Two Servers,
1st 2nd
database a --> after replication a'
b' <-- after replication b
on 1st server a is replicated to 2nd
on 2nd server b is replicated to 1st
I tried with merge replication it is working fine.But if i tried to use
transactional replication it give some error like "access
violation".Using tansactional replication is it possible.plz help me to
slove this problem.
Regards,
Senthil prabu R
When you used sp_addsubscription, did you set @.loopback_detection=true?
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
"senthilprabu" <senthilpraburaju@.gmail.com> wrote in message
news:1125116841.951132.162370@.o13g2000cwo.googlegr oups.com...
> Two Servers,
> 1st 2nd
> database a --> after replication a'
> b' <-- after replication b
> on 1st server a is replicated to 2nd
> on 2nd server b is replicated to 1st
> I tried with merge replication it is working fine.But if i tried to use
> transactional replication it give some error like "access
> violation".Using tansactional replication is it possible.plz help me to
> slove this problem.
> Regards,
> Senthil prabu R
>

Bi-directional Transaction Replication

Two Servers,

1st 2nd
database a ---> after replication a'
b' <--- after replication b

on 1st server a is replicated to 2nd
on 2nd server b is replicated to 1st

I tried with merge replication it is working fine.But if i tried to use
transactional replication it give some error like "access
violation".Using tansactional replication is it possible.plz help me to
slove this problem.

Regards,
Senthil prabu RI have no idea myself, but under "Nonpartitioned, Bidirectional,
Transactional Replication", Books Online says that to implement
bidirectional transactional replication "substantial customization and
programming is usually required."

So you might get a better response in
microsoft.public.sqlserver.replication, as it seems to be something
quite specialized.

Simon

bi directional transactional replication and subscriber name

Hi,
I am trying to setup bi-directional transactional replication between 2 SQL Servers,
I add the subscription on both the servers using the code below.

EXEC sp_addsubscription @.publication = N'test',
@.article = N'all', @.subscriber = 'AnyServer',
@.destination_db = N'test', @.sync_type = N'none',
@.status = N'active', @.update_mode = N'read only',
@.loopback_detection = 'true'
GO

I have defined 'AnyServer' on both the servers using cliconfg
(Server1's AnyServer pointed towards Server2 and Server2's AnyServer pointed towards Server1, I need to do that because there is a restriction to run the same code on both the servers),

After inserting a record on server1 in the 'test' database, the changes successfully transfers to the server2, then server2 sends it back to server1 and server1 generates the error of

"Violation of PRIMARY KEY constraint 'PK_Table1'. Cannot insert duplicate key in object 'Table1'."

It seems as if loop detection is failing if I keep the same subscriber name on both sides.

It runs fine when I change the subscriber name in the subscription (@.subscriber = 'Server2' for server1 and @.subscriber = 'Server1' for server2).

Can anybody explains this behavior to me?not really , but common sense says that if you try & create a duplicate primary key on the first server, it MUST fail to preserve PK integrity...|||Let me rephrase my question.....

Why bi-directional replication is failing with the same subscriber name (managed by cliconfg) on both sides.... when it is running fine with the real server names?

Between database servers

Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Hi all,
> > I like to access a production database server from my development database,
> > I need to count how fast one table in the production table grows, is there
> > any way to do it without actually getting on the production server? linked
> > server?
> >
> > Thanks.|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
> > Yes you can create a linked server
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> > "Matthew Z" wrote:
> >
> > > Hi all,
> > > I like to access a production database server from my development database,
> > > I need to count how fast one table in the production table grows, is there
> > > any way to do it without actually getting on the production server? linked
> > > server?
> > >
> > > Thanks.|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Thanks. Because the company security reseans, I can't use the linked server,
> > is there a way to work around?
> >
> >
> > "SQL" wrote:
> >
> > > Yes you can create a linked server
> > >
> > > http://sqlservercode.blogspot.com/
> > >
> > >
> > > "Matthew Z" wrote:
> > >
> > > > Hi all,
> > > > I like to access a production database server from my development database,
> > > > I need to count how fast one table in the production table grows, is there
> > > > any way to do it without actually getting on the production server? linked
> > > > server?
> > > >
> > > > Thanks.|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>> look up OPENROWSET and OPENDATASOURCE in Books on line
>> http://sqlservercode.blogspot.com/
>>
>> "Matthew Z" wrote:
>> > Thanks. Because the company security reseans, I can't use the linked
>> > server,
>> > is there a way to work around?
>> >
>> >
>> > "SQL" wrote:
>> >
>> > > Yes you can create a linked server
>> > >
>> > > http://sqlservercode.blogspot.com/
>> > >
>> > >
>> > > "Matthew Z" wrote:
>> > >
>> > > > Hi all,
>> > > > I like to access a production database server from my development
>> > > > database,
>> > > > I need to count how fast one table in the production table grows,
>> > > > is there
>> > > > any way to do it without actually getting on the production server?
>> > > > linked
>> > > > server?
>> > > >
>> > > > Thanks.|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
> > look up OPENROWSET and OPENDATASOURCE in Books on line
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> >
> > "Matthew Z" wrote:
> >
> > > Thanks. Because the company security reseans, I can't use the linked server,
> > > is there a way to work around?
> > >
> > >
> > > "SQL" wrote:
> > >
> > > > Yes you can create a linked server
> > > >
> > > > http://sqlservercode.blogspot.com/
> > > >
> > > >
> > > > "Matthew Z" wrote:
> > > >
> > > > > Hi all,
> > > > > I like to access a production database server from my development database,
> > > > > I need to count how fast one table in the production table grows, is there
> > > > > any way to do it without actually getting on the production server? linked
> > > > > server?
> > > > >
> > > > > Thanks.|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.

Between database servers

Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.
Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:

> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:

> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.

Between database servers

Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:

> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked serve
r,
> is there a way to work around?
>
> "SQL" wrote:
>|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have acce
ss
> from one server to a different server.
> "SQL" wrote:
>|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:

> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.

Best way to update different servers

We are looking at combining some systems that would allow our Sql Server
update the other systems Sql Server system when certain events happens -
names change for instance.
Looking at using Web Services as one way of handling the Data movement
between the 2 systems.
But if they are both Sql Servers, is there some way, through the use of
Triggers for instance, that we could call a stored procedure on another
server (not at our location) to do the updates. Web Services are fine, but
we still have the push/pull/polling problem of knowing when to update System
B when System A's data changes and vice versa.
Is it best to do this outside of the Sql world or would it be reasonable to
do this from Sql to Sql which I assume would be more efficient?
Thanks,
TomThere is nothing to stop you from updating another SQL Server as a result of
an event on your SQL Server. Triggers seem to be the most obvious.
Ideally, you would have a stored proc on the linked server and then execute
something like:
MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
We are looking at combining some systems that would allow our Sql Server
update the other systems Sql Server system when certain events happens -
names change for instance.
Looking at using Web Services as one way of handling the Data movement
between the 2 systems.
But if they are both Sql Servers, is there some way, through the use of
Triggers for instance, that we could call a stored procedure on another
server (not at our location) to do the updates. Web Services are fine, but
we still have the push/pull/polling problem of knowing when to update System
B when System A's data changes and vice versa.
Is it best to do this outside of the Sql world or would it be reasonable to
do this from Sql to Sql which I assume would be more efficient?
Thanks,
Tom|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtY0lv3oFHA.1444@.TK2MSFTNGP10.phx.gbl...
> There is nothing to stop you from updating another SQL Server as a result
> of
> an event on your SQL Server. Triggers seem to be the most obvious.
> Ideally, you would have a stored proc on the linked server and then
> execute
> something like:
> MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Would you have to set up each server as a linked server on each others
machines?
Would this be the best way even if we are doing this over the Internet?
Thanks,
Tom
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
> We are looking at combining some systems that would allow our Sql Server
> update the other systems Sql Server system when certain events happens -
> names change for instance.
> Looking at using Web Services as one way of handling the Data movement
> between the 2 systems.
> But if they are both Sql Servers, is there some way, through the use of
> Triggers for instance, that we could call a stored procedure on another
> server (not at our location) to do the updates. Web Services are fine,
> but
> we still have the push/pull/polling problem of knowing when to update
> System
> B when System A's data changes and vice versa.
> Is it best to do this outside of the Sql world or would it be reasonable
> to
> do this from Sql to Sql which I assume would be more efficient?
> Thanks,
> Tom
>|||Would you have to set up each server as a linked server on each others
machines?

Would this be the best way even if we are doing this over the Internet?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:elYgqz3oFHA.708@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtY0lv3oFHA.1444@.TK2MSFTNGP10.phx.gbl...
> There is nothing to stop you from updating another SQL Server as a result
> of
> an event on your SQL Server. Triggers seem to be the most obvious.
> Ideally, you would have a stored proc on the linked server and then
> execute
> something like:
> MyLinkedServer.MyDB.dbo.MyProc @.ID, 'The New Name'
Would you have to set up each server as a linked server on each others
machines?
Would this be the best way even if we are doing this over the Internet?
Thanks,
Tom
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O5kXts3oFHA.1148@.TK2MSFTNGP12.phx.gbl...
> We are looking at combining some systems that would allow our Sql Server
> update the other systems Sql Server system when certain events happens -
> names change for instance.
> Looking at using Web Services as one way of handling the Data movement
> between the 2 systems.
> But if they are both Sql Servers, is there some way, through the use of
> Triggers for instance, that we could call a stored procedure on another
> server (not at our location) to do the updates. Web Services are fine,
> but
> we still have the push/pull/polling problem of knowing when to update
> System
> B when System A's data changes and vice versa.
> Is it best to do this outside of the Sql world or would it be reasonable
> to
> do this from Sql to Sql which I assume would be more efficient?
> Thanks,
> Tom
>

Best way to mirror data?

I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
I'm constantly exporting/importing across from one to the other using the
export/import tool within SQL Enterprise Manager. I'm consistently having
to update "default values" etc. as they are wiped clean when I
import/export.
I could do a script dump in SQL Query Analyzer for each table, but I was
figuring there had to be a better way to do this...?How about a job to do a backup on one, copy it across the network, then
restore on server 2? You also have the copy database wizard in DTS although
I've never used it
Ray Higdon MCSE, MCDBA, CCNA
--
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can try SQL Compare from Red-Gate. It isn't perfect, but it is better
than most 'roll your own' systems that I have seen, including my own.
www.red-gate.com
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||Check out DB Ghost from Innovartis at http://www.dbghost.com
Darren Fuller
SQL Server DBA MCSE
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Best way to mirror data?

I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
I'm constantly exporting/importing across from one to the other using the
export/import tool within SQL Enterprise Manager. I'm consistently having
to update "default values" etc. as they are wiped clean when I
import/export.
I could do a script dump in SQL Query Analyzer for each table, but I was
figuring there had to be a better way to do this...?How about a job to do a backup on one, copy it across the network, then
restore on server 2? You also have the copy database wizard in DTS although
I've never used it
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can try SQL Compare from Red-Gate. It isn't perfect, but it is better
than most 'roll your own' systems that I have seen, including my own.
www.red-gate.com
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jesse Bilsten" <jesse@.vreo.com> wrote in message
news:OaExfP%23$DHA.688@.tk2msftngp13.phx.gbl...
> I've got two SQL servers (sql server 2000 version 8.0) (dev) and (live).
> I'm constantly exporting/importing across from one to the other using the
> export/import tool within SQL Enterprise Manager. I'm consistently having
> to update "default values" etc. as they are wiped clean when I
> import/export.
> I could do a script dump in SQL Query Analyzer for each table, but I was
> figuring there had to be a better way to do this...?
>
>|||You can easily create a job in SQL Server Agent.
>--Original Message--
>I've got two SQL servers (sql server 2000 version 8.0)
(dev) and (live).
>I'm constantly exporting/importing across from one to the
other using the
>export/import tool within SQL Enterprise Manager. I'm
consistently having
>to update "default values" etc. as they are wiped clean
when I
>import/export.
>I could do a script dump in SQL Query Analyzer for each
table, but I was
>figuring there had to be a better way to do this...?
>
>.
>

Best way to keep a database synched with a server that is outside the firewall

Hi,
We have two servers, one inside the firewall and one outside and need to
keep them synched up on a daily basis. Short of doing full backup and
restore, what is best solution?
Thanks.
<a> wrote in message news:eE7ukKlcHHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have two servers, one inside the firewall and one outside and need to
> keep them synched up on a daily basis. Short of doing full backup and
> restore, what is best solution?
Depends on what you need.
If you need read-only, I'd go with either replication or log-shipping.
With log-shipping just create a stand-by file between loading each log file.
You'll be able to read from the database.
Shipping just the log files should be smaller than a full-backup and
restore.
If you go with replication, you're probably going to have to open up your
firewall a bit more and be a bit more careful about security.
But then you won't get kicked out each time changes are made on the outside
server.

> Thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thank for the response. Can I set up log shipping or replication when the
primary and secondary server can not even see each other? The only port
open on the primary server's firewall is 80. Thanks again.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:euMRAfncHHA.4772@.TK2MSFTNGP05.phx.gbl...
> <a> wrote in message news:eE7ukKlcHHA.3484@.TK2MSFTNGP04.phx.gbl...
> Depends on what you need.
> If you need read-only, I'd go with either replication or log-shipping.
> With log-shipping just create a stand-by file between loading each log
> file. You'll be able to read from the database.
> Shipping just the log files should be smaller than a full-backup and
> restore.
> If you go with replication, you're probably going to have to open up your
> firewall a bit more and be a bit more careful about security.
> But then you won't get kicked out each time changes are made on the
> outside server.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||<a> wrote in message news:uM2c7hucHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Thank for the response. Can I set up log shipping or replication when the
> primary and secondary server can not even see each other? The only port
> open on the primary server's firewall is 80. Thanks again.
Hmm... not out of the box.
Realistically you probably could do this through port 80, but it would be a
mess.
One possible way, though a bit more complicated and error prone, would be to
log-ship the files via FTP.

> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:euMRAfncHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Best way to keep a database synched with a server that is outside the firewall

Hi,
We have two servers, one inside the firewall and one outside and need to
keep them synched up on a daily basis. Short of doing full backup and
restore, what is best solution?
Thanks.<a> wrote in message news:eE7ukKlcHHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have two servers, one inside the firewall and one outside and need to
> keep them synched up on a daily basis. Short of doing full backup and
> restore, what is best solution?
Depends on what you need.
If you need read-only, I'd go with either replication or log-shipping.
With log-shipping just create a stand-by file between loading each log file.
You'll be able to read from the database.
Shipping just the log files should be smaller than a full-backup and
restore.
If you go with replication, you're probably going to have to open up your
firewall a bit more and be a bit more careful about security.
But then you won't get kicked out each time changes are made on the outside
server.

> Thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thank for the response. Can I set up log shipping or replication when the
primary and secondary server can not even see each other? The only port
open on the primary server's firewall is 80. Thanks again.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:euMRAfncHHA.4772@.TK2MSFTNGP05.phx.gbl...
> <a> wrote in message news:eE7ukKlcHHA.3484@.TK2MSFTNGP04.phx.gbl...
> Depends on what you need.
> If you need read-only, I'd go with either replication or log-shipping.
> With log-shipping just create a stand-by file between loading each log
> file. You'll be able to read from the database.
> Shipping just the log files should be smaller than a full-backup and
> restore.
> If you go with replication, you're probably going to have to open up your
> firewall a bit more and be a bit more careful about security.
> But then you won't get kicked out each time changes are made on the
> outside server.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||<a> wrote in message news:uM2c7hucHHA.4624@.TK2MSFTNGP03.phx.gbl...
> Thank for the response. Can I set up log shipping or replication when the
> primary and secondary server can not even see each other? The only port
> open on the primary server's firewall is 80. Thanks again.
Hmm... not out of the box.
Realistically you probably could do this through port 80, but it would be a
mess.
One possible way, though a bit more complicated and error prone, would be to
log-ship the files via FTP.

> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message news:euMRAfncHHA.4772@.TK2MSFTNGP05.phx.gbl...
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html