Question on SQL

Good Day,
I have a simple question on SQL 2000. Please note that I am no expert and
this is a basic question.
We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a remote
site that connects to this server via a T1 line using a program that we
created called popss.
What happens is that the program takes a long time getting the data from the
server and we know that it's a bandwidth issue. What I would like to know
is there a way to install a toned down version of SQL something like desktop
on another Windows 2003 server or do we need the another full version of SQL
at that location so that the program can read from the local instance
instead of going over the pipe.
I am looking for a way to sync up the two SQL databases say at night this
way there we be no traffic over the pipe during the day.
Thank You
Adam RaffYou could use Transactional Replication and set its schedule as running once
at night. SQL Server Express Edition can be used as a Subscriber in a
Replication topology and it's free. However, Express Edition has its
limitations. (4GB db, 1 CPU, 1GB RAM)
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
You would make your current SQL Server as Publisher \ Distributor (consider
workload on your current server) and the other one would be the Subscriber.
So, your new SQL Server would be updated daily and your app would query your
local SQL Server directly.
Try this in a test environment first. If your system and app works without
any problem then you could apply it to your production system.
--
Ekrem Önsoy
"Adam Raff" <araff@.newsgroup.nospam> wrote in message
news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> Good Day,
> I have a simple question on SQL 2000. Please note that I am no expert and
> this is a basic question.
> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
> remote site that connects to this server via a T1 line using a program
> that we created called popss.
> What happens is that the program takes a long time getting the data from
> the server and we know that it's a bandwidth issue. What I would like to
> know is there a way to install a toned down version of SQL something like
> desktop on another Windows 2003 server or do we need the another full
> version of SQL at that location so that the program can read from the
> local instance instead of going over the pipe.
> I am looking for a way to sync up the two SQL databases say at night this
> way there we be no traffic over the pipe during the day.
> Thank You
> Adam Raff
>|||Ekrem,
Thanks for your help. The size of the database is small about 500MB so that
is not an issue. My only other question on this is Does this go both ways.
Meaning when they copy to the SQL Express will it update the SQL server
later during the Replication time since the Express is setup as Subscriber?
Thanks again for your help
Adam Raff
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
> You could use Transactional Replication and set its schedule as running
> once at night. SQL Server Express Edition can be used as a Subscriber in a
> Replication topology and it's free. However, Express Edition has its
> limitations. (4GB db, 1 CPU, 1GB RAM)
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> You would make your current SQL Server as Publisher \ Distributor
> (consider workload on your current server) and the other one would be the
> Subscriber. So, your new SQL Server would be updated daily and your app
> would query your local SQL Server directly.
> Try this in a test environment first. If your system and app works without
> any problem then you could apply it to your production system.
> --
> Ekrem Önsoy
>
> "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I have a simple question on SQL 2000. Please note that I am no expert
>> and this is a basic question.
>> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
>> remote site that connects to this server via a T1 line using a program
>> that we created called popss.
>> What happens is that the program takes a long time getting the data from
>> the server and we know that it's a bandwidth issue. What I would like to
>> know is there a way to install a toned down version of SQL something like
>> desktop on another Windows 2003 server or do we need the another full
>> version of SQL at that location so that the program can read from the
>> local instance instead of going over the pipe.
>> I am looking for a way to sync up the two SQL databases say at night this
>> way there we be no traffic over the pipe during the day.
>> Thank You
>> Adam Raff
>|||Dear Adam,
Thank you for posting here.
I would like to explain that the SQL Server 2000 does not support the
updatable Subscriptions for Transactional Replication and the SQL Server
2005 Express 2005 Edition can only be configured as the Subscriber. So, the
replication is done in only one way and the data written in the SQL Express
will not be transferred to the main SQL Server.
If your application "Popss" only reads the data from SQL Server, we can
choose to use Transactional Replication. If not, we can try the Merge
Replication so that the changes can be synchronized to the publisher.
For your reference, I have included some relevant articles below:
How Merge Replication Works
http://technet.microsoft.com/en-us/library/ms151329.aspx
How Transactional Replication Works
http://technet.microsoft.com/en-us/library/ms151706.aspx
If anything is unclear in my reply, please don't hesitate to let me know
and I will be glad to help.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Adam Raff" <araff@.newsgroup.nospam>
| References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
<11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
| Subject: Re: Question on SQL
| Date: Wed, 12 Sep 2007 16:40:10 -0400
| Lines: 62
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
| X-RFC2646: Format=Flowed; Response
| Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net 208.39.138.189
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Ekrem,
|
| Thanks for your help. The size of the database is small about 500MB so
that
| is not an issue. My only other question on this is Does this go both
ways.
|
| Meaning when they copy to the SQL Express will it update the SQL server
| later during the Replication time since the Express is setup as
Subscriber?
|
| Thanks again for your help
| Adam Raff
|
|
|
| "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
| news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
| > You could use Transactional Replication and set its schedule as running
| > once at night. SQL Server Express Edition can be used as a Subscriber
in a
| > Replication topology and it's free. However, Express Edition has its
| > limitations. (4GB db, 1 CPU, 1GB RAM)
| >
| > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
| >
| > You would make your current SQL Server as Publisher \ Distributor
| > (consider workload on your current server) and the other one would be
the
| > Subscriber. So, your new SQL Server would be updated daily and your app
| > would query your local SQL Server directly.
| >
| > Try this in a test environment first. If your system and app works
without
| > any problem then you could apply it to your production system.
| >
| > --
| > Ekrem Önsoy
| >
| >
| >
| > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
| > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
| >> Good Day,
| >>
| >> I have a simple question on SQL 2000. Please note that I am no expert
| >> and this is a basic question.
| >>
| >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
| >> remote site that connects to this server via a T1 line using a program
| >> that we created called popss.
| >>
| >> What happens is that the program takes a long time getting the data
from
| >> the server and we know that it's a bandwidth issue. What I would like
to
| >> know is there a way to install a toned down version of SQL something
like
| >> desktop on another Windows 2003 server or do we need the another full
| >> version of SQL at that location so that the program can read from the
| >> local instance instead of going over the pipe.
| >>
| >> I am looking for a way to sync up the two SQL databases say at night
this
| >> way there we be no traffic over the pipe during the day.
| >>
| >> Thank You
| >> Adam Raff
| >>
| >
|
|
||||Hi Adams,
If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
this then work then?
Is the Express version limited in that it can never sync up with the full
version of SQL?
Thanks
Adam Raff
"Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
> Dear Adam,
> Thank you for posting here.
> I would like to explain that the SQL Server 2000 does not support the
> updatable Subscriptions for Transactional Replication and the SQL Server
> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
> the
> replication is done in only one way and the data written in the SQL
> Express
> will not be transferred to the main SQL Server.
> If your application "Popss" only reads the data from SQL Server, we can
> choose to use Transactional Replication. If not, we can try the Merge
> Replication so that the changes can be synchronized to the publisher.
> For your reference, I have included some relevant articles below:
> How Merge Replication Works
> http://technet.microsoft.com/en-us/library/ms151329.aspx
> How Transactional Replication Works
> http://technet.microsoft.com/en-us/library/ms151706.aspx
> If anything is unclear in my reply, please don't hesitate to let me know
> and I will be glad to help.
> Have a nice day!
> Best regards,
> Adams Qu, MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> | From: "Adam Raff" <araff@.newsgroup.nospam>
> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
> | Subject: Re: Question on SQL
> | Date: Wed, 12 Sep 2007 16:40:10 -0400
> | Lines: 62
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net 208.39.138.189
> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Ekrem,
> |
> | Thanks for your help. The size of the database is small about 500MB so
> that
> | is not an issue. My only other question on this is Does this go both
> ways.
> |
> | Meaning when they copy to the SQL Express will it update the SQL server
> | later during the Replication time since the Express is setup as
> Subscriber?
> |
> | Thanks again for your help
> | Adam Raff
> |
> |
> |
> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
> | > You could use Transactional Replication and set its schedule as
> running
> | > once at night. SQL Server Express Edition can be used as a Subscriber
> in a
> | > Replication topology and it's free. However, Express Edition has its
> | > limitations. (4GB db, 1 CPU, 1GB RAM)
> | >
> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
> | >
> | > You would make your current SQL Server as Publisher \ Distributor
> | > (consider workload on your current server) and the other one would be
> the
> | > Subscriber. So, your new SQL Server would be updated daily and your
> app
> | > would query your local SQL Server directly.
> | >
> | > Try this in a test environment first. If your system and app works
> without
> | > any problem then you could apply it to your production system.
> | >
> | > --
> | > Ekrem Önsoy
> | >
> | >
> | >
> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
> | >> Good Day,
> | >>
> | >> I have a simple question on SQL 2000. Please note that I am no
> expert
> | >> and this is a basic question.
> | >>
> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have a
> | >> remote site that connects to this server via a T1 line using a
> program
> | >> that we created called popss.
> | >>
> | >> What happens is that the program takes a long time getting the data
> from
> | >> the server and we know that it's a bandwidth issue. What I would
> like
> to
> | >> know is there a way to install a toned down version of SQL something
> like
> | >> desktop on another Windows 2003 server or do we need the another full
> | >> version of SQL at that location so that the program can read from the
> | >> local instance instead of going over the pipe.
> | >>
> | >> I am looking for a way to sync up the two SQL databases say at night
> this
> | >> way there we be no traffic over the pipe during the day.
> | >>
> | >> Thank You
> | >> Adam Raff
> | >>
> | >
> |
> |
> |
>|||Correction: There is no version 2007 of SQL Server. SQL Server 2008 is the
one that's going to be successor of SQL Server 2005.
If you want to transfer changes that you make in your second SQL Server to
be applied to your first SQL Server, then use Merge Replication. As you can
not use SQL Server Express Edition in a Updatable Subscriptions for
Transactional Replication topology, you may want to go with Merge
Replication. You'd be able to use Express Edition in a Transactional
Replication if you wanted to use your second SQL Server only for reporting
server purposes which means you would not want to transfer the updated
records from your second SQL Server to the First one.
Express Edition's limitation is just to be Subscriber in a Replication
Topology in this topic. It can not be a Publisher nor Distributor. But it
can be a Subscriber and for you situation can be used in a Merge Replication
topology.
Just give it a try Merge Replication on your test machine and see how it
works.
If you'd like to learn more about Transactional and Merge Replications, I
encourage you you to visit the links that Adams already mentioned.
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
Ekrem Önsoy
"Adam Raff" <araff@.newsgroup.nospam> wrote in message
news:eVK6zng9HHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi Adams,
> If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
> this then work then?
> Is the Express version limited in that it can never sync up with the full
> version of SQL?
> Thanks
> Adam Raff
>
> "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
> news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
>> Dear Adam,
>> Thank you for posting here.
>> I would like to explain that the SQL Server 2000 does not support the
>> updatable Subscriptions for Transactional Replication and the SQL Server
>> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
>> the
>> replication is done in only one way and the data written in the SQL
>> Express
>> will not be transferred to the main SQL Server.
>> If your application "Popss" only reads the data from SQL Server, we can
>> choose to use Transactional Replication. If not, we can try the Merge
>> Replication so that the changes can be synchronized to the publisher.
>> For your reference, I have included some relevant articles below:
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>> If anything is unclear in my reply, please don't hesitate to let me know
>> and I will be glad to help.
>> Have a nice day!
>> Best regards,
>> Adams Qu, MCSE, MCDBA, MCTS
>> Microsoft Online Support
>> Microsoft Global Technical Support Center
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>> | From: "Adam Raff" <araff@.newsgroup.nospam>
>> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
>> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
>> | Subject: Re: Question on SQL
>> | Date: Wed, 12 Sep 2007 16:40:10 -0400
>> | Lines: 62
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net
>> 208.39.138.189
>> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
>> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | Ekrem,
>> |
>> | Thanks for your help. The size of the database is small about 500MB so
>> that
>> | is not an issue. My only other question on this is Does this go both
>> ways.
>> |
>> | Meaning when they copy to the SQL Express will it update the SQL server
>> | later during the Replication time since the Express is setup as
>> Subscriber?
>> |
>> | Thanks again for your help
>> | Adam Raff
>> |
>> |
>> |
>> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
>> | > You could use Transactional Replication and set its schedule as
>> running
>> | > once at night. SQL Server Express Edition can be used as a Subscriber
>> in a
>> | > Replication topology and it's free. However, Express Edition has its
>> | > limitations. (4GB db, 1 CPU, 1GB RAM)
>> | >
>> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
>> | >
>> | > You would make your current SQL Server as Publisher \ Distributor
>> | > (consider workload on your current server) and the other one would be
>> the
>> | > Subscriber. So, your new SQL Server would be updated daily and your
>> app
>> | > would query your local SQL Server directly.
>> | >
>> | > Try this in a test environment first. If your system and app works
>> without
>> | > any problem then you could apply it to your production system.
>> | >
>> | > --
>> | > Ekrem Önsoy
>> | >
>> | >
>> | >
>> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
>> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> | >> Good Day,
>> | >>
>> | >> I have a simple question on SQL 2000. Please note that I am no
>> expert
>> | >> and this is a basic question.
>> | >>
>> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have
>> a
>> | >> remote site that connects to this server via a T1 line using a
>> program
>> | >> that we created called popss.
>> | >>
>> | >> What happens is that the program takes a long time getting the data
>> from
>> | >> the server and we know that it's a bandwidth issue. What I would
>> like
>> to
>> | >> know is there a way to install a toned down version of SQL something
>> like
>> | >> desktop on another Windows 2003 server or do we need the another
>> full
>> | >> version of SQL at that location so that the program can read from
>> the
>> | >> local instance instead of going over the pipe.
>> | >>
>> | >> I am looking for a way to sync up the two SQL databases say at night
>> this
>> | >> way there we be no traffic over the pipe during the day.
>> | >>
>> | >> Thank You
>> | >> Adam Raff
>> | >>
>> | >
>> |
>> |
>> |
>|||Thank You both for explaining this to me. I will sit down to read the
articles to get an idea. Now that I know that it is possible to do with
what I have.
Thanks again for your help
Adam Raff
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:8E4680AF-987C-4999-90A3-33BF3B6D5953@.microsoft.com...
> Correction: There is no version 2007 of SQL Server. SQL Server 2008 is the
> one that's going to be successor of SQL Server 2005.
> If you want to transfer changes that you make in your second SQL Server to
> be applied to your first SQL Server, then use Merge Replication. As you
> can not use SQL Server Express Edition in a Updatable Subscriptions for
> Transactional Replication topology, you may want to go with Merge
> Replication. You'd be able to use Express Edition in a Transactional
> Replication if you wanted to use your second SQL Server only for reporting
> server purposes which means you would not want to transfer the updated
> records from your second SQL Server to the First one.
> Express Edition's limitation is just to be Subscriber in a Replication
> Topology in this topic. It can not be a Publisher nor Distributor. But it
> can be a Subscriber and for you situation can be used in a Merge
> Replication topology.
> Just give it a try Merge Replication on your test machine and see how it
> works.
> If you'd like to learn more about Transactional and Merge Replications, I
> encourage you you to visit the links that Adams already mentioned.
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>
> --
> Ekrem Önsoy
>
> "Adam Raff" <araff@.newsgroup.nospam> wrote in message
> news:eVK6zng9HHA.1212@.TK2MSFTNGP05.phx.gbl...
>> Hi Adams,
>> If we upgrade to SQL 2005 lets say or the newer version of SQL 2007 will
>> this then work then?
>> Is the Express version limited in that it can never sync up with the full
>> version of SQL?
>> Thanks
>> Adam Raff
>>
>> "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
>> news:WwE9G5d9HHA.5532@.TK2MSFTNGHUB02.phx.gbl...
>> Dear Adam,
>> Thank you for posting here.
>> I would like to explain that the SQL Server 2000 does not support the
>> updatable Subscriptions for Transactional Replication and the SQL Server
>> 2005 Express 2005 Edition can only be configured as the Subscriber. So,
>> the
>> replication is done in only one way and the data written in the SQL
>> Express
>> will not be transferred to the main SQL Server.
>> If your application "Popss" only reads the data from SQL Server, we can
>> choose to use Transactional Replication. If not, we can try the Merge
>> Replication so that the changes can be synchronized to the publisher.
>> For your reference, I have included some relevant articles below:
>> How Merge Replication Works
>> http://technet.microsoft.com/en-us/library/ms151329.aspx
>> How Transactional Replication Works
>> http://technet.microsoft.com/en-us/library/ms151706.aspx
>> If anything is unclear in my reply, please don't hesitate to let me know
>> and I will be glad to help.
>> Have a nice day!
>> Best regards,
>> Adams Qu, MCSE, MCDBA, MCTS
>> Microsoft Online Support
>> Microsoft Global Technical Support Center
>> Get Secure! - www.microsoft.com/security
>> =====================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> --
>> | From: "Adam Raff" <araff@.newsgroup.nospam>
>> | References: <OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl>
>> <11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com>
>> | Subject: Re: Question on SQL
>> | Date: Wed, 12 Sep 2007 16:40:10 -0400
>> | Lines: 62
>> | X-Priority: 3
>> | X-MSMail-Priority: Normal
>> | X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138
>> | X-RFC2646: Format=Flowed; Response
>> | Message-ID: <OyQZd0X9HHA.1212@.TK2MSFTNGP05.phx.gbl>
>> | Newsgroups: microsoft.public.sqlserver.server
>> | NNTP-Posting-Host: 208-39-138-189.isp.comcastbusiness.net
>> 208.39.138.189
>> | Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP05.phx.gbl
>> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:25271
>> | X-Tomcat-NG: microsoft.public.sqlserver.server
>> |
>> | Ekrem,
>> |
>> | Thanks for your help. The size of the database is small about 500MB
>> so
>> that
>> | is not an issue. My only other question on this is Does this go both
>> ways.
>> |
>> | Meaning when they copy to the SQL Express will it update the SQL
>> server
>> | later during the Replication time since the Express is setup as
>> Subscriber?
>> |
>> | Thanks again for your help
>> | Adam Raff
>> |
>> |
>> |
>> | "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> | news:11330C0D-7942-4968-BB9F-230F6FD3EA83@.microsoft.com...
>> | > You could use Transactional Replication and set its schedule as
>> running
>> | > once at night. SQL Server Express Edition can be used as a
>> Subscriber
>> in a
>> | > Replication topology and it's free. However, Express Edition has its
>> | > limitations. (4GB db, 1 CPU, 1GB RAM)
>> | >
>> | > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
>> | >
>> | > You would make your current SQL Server as Publisher \ Distributor
>> | > (consider workload on your current server) and the other one would
>> be
>> the
>> | > Subscriber. So, your new SQL Server would be updated daily and your
>> app
>> | > would query your local SQL Server directly.
>> | >
>> | > Try this in a test environment first. If your system and app works
>> without
>> | > any problem then you could apply it to your production system.
>> | >
>> | > --
>> | > Ekrem Önsoy
>> | >
>> | >
>> | >
>> | > "Adam Raff" <araff@.newsgroup.nospam> wrote in message
>> | > news:OZ7NvdW9HHA.4752@.TK2MSFTNGP04.phx.gbl...
>> | >> Good Day,
>> | >>
>> | >> I have a simple question on SQL 2000. Please note that I am no
>> expert
>> | >> and this is a basic question.
>> | >>
>> | >> We have a SQL 2000 SP3 server running on Windows 2003 SP1. We have
>> a
>> | >> remote site that connects to this server via a T1 line using a
>> program
>> | >> that we created called popss.
>> | >>
>> | >> What happens is that the program takes a long time getting the data
>> from
>> | >> the server and we know that it's a bandwidth issue. What I would
>> like
>> to
>> | >> know is there a way to install a toned down version of SQL
>> something
>> like
>> | >> desktop on another Windows 2003 server or do we need the another
>> full
>> | >> version of SQL at that location so that the program can read from
>> the
>> | >> local instance instead of going over the pipe.
>> | >>
>> | >> I am looking for a way to sync up the two SQL databases say at
>> night
>> this
>> | >> way there we be no traffic over the pipe during the day.
>> | >>
>> | >> Thank You
>> | >> Adam Raff
>> | >>
>> | >
>> |
>> |
>> |
>>
>|||Dear Adam,
Thank you for your response.
If you have any other questions or concerns, please do not hesitate to
contact me. It is always my pleasure to be of assistance.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Question on copying over overlapping data from one database to another...

... and preserving the relationships.
(Note, this is more of a SQL question than a SQL-related ASP.NET question...)
Say I have two databases, D1 and D2, with the same three tables:
Companies
Departments
Employees
With the standard one-to-many relationships down the line, with each
table having a PK, IDENTITY field like CompanyID, DepartmentID, and
EmployeeID.

I have a smattering of data in each of D1 and D2 for these tables with
overlaps in the ID field values. What I want to be able to do is copy
over D1's data to D2, preserving the relationships in D1 even though
there are ID overlaps in D2. So the tool I'd use would have to be
smart enough to check for ID dups in D2 and appropriate change the ID
values in D1's tables, maintaining the relationships.
Is there some built-in SQL tool to do this or do I have to do this myself?
Thanks!

There may be 3rd Party tools that do this but there's not any way, I'm aware of, of doing it in Enterprise Manager or Management Server Express. I do have an idea, though, of a way you could at least merge the tables using standard SQL:

For instance, if we take the Northwind database and imagine you have two Products tables in it, both with the exact same columns and both populated with data where the ProductID (PK) might be the same in both tables. We shall call these two tablesProducts andProducts2. Now, if you wanted to merge these two tables into one new table with a new, unqiue primary key you would do this:

Create a new table called (say) Products_Temp with the same structure as Products and make sure that ProductID is set as an Identity column. The easiest way is to create a script from Products table which would look like:

CREATETABLE [dbo].[Products](

[ProductID] [int]IDENTITY(1,1)NOTNULL,

[ProductName] [nvarchar](40)COLLATE Latin1_General_CI_ASNOTNULL,

[SupplierID] [int]NULL,

[CategoryID] [int]NULL,

[QuantityPerUnit] [nvarchar](20)COLLATE Latin1_General_CI_ASNULL,

[UnitPrice] [money]NULLCONSTRAINT [DF_Products_UnitPrice]DEFAULT((0)),

[UnitsInStock] [smallint]NULLCONSTRAINT [DF_Products_UnitsInStock]DEFAULT((0)),

[UnitsOnOrder] [smallint]NULLCONSTRAINT [DF_Products_UnitsOnOrder]DEFAULT((0)),

[ReorderLevel] [smallint]NULLCONSTRAINT [DF_Products_ReorderLevel]DEFAULT((0)),

[Discontinued] [bit]NOTNULLCONSTRAINT [DF_Products_Discontinued]DEFAULT((0)),

CONSTRAINT [PK_Products]PRIMARYKEYCLUSTERED

(

[ProductID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

Then to merge the two tables you would UNION them together on all columnsexceptthe primary key column (ProductID) and place the results into the Products_Temp table you just created. Eg.

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

INTO Product_TEMP

FROM

(SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products

UNION

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products2)

AS Temp;

Now Products_Temp will contain the merged data from both tables (with any exact duplicate rows removed) and each row will now have a unique primary key (because of Identity insert). I know this isn't that much help because it doesn't preserve the PK > FK relationships between multiple tables, but it might give you some ideas or inspiration... Good luck :)

|||Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.
|||Connect, I was not aware of any built-in functionality in SQL Server 2000, and figured it would have to be scripted.
Does anyone know of a third-party tool that accomplishes this?

Blake05 wrote:

Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.

Blake05, how can I use EM to copy data from one DB to another when there are overlapping ID values between the two databases' tables?
Thanks
|||Yes you can
|||You don't need third party tool create Unique index with IGNORE_DUP_KEY option and do standard INSERT INTO and in SQL Server 2000 and below that statement will not affect update statements. What happens is SQL Server will not insert the duplicate values it will insert only the unique values. I am assuming this is what you want and run a search for the above in SQL Server BOL (books online). Hope this helps.