Bi-Directional Updating Databases on same SQL server

I am trying to separate two applications sharing the same database and a few tables(like t_employee) in a phased approach. How can I best setup a bi-directional update of this t_employee table when updated by either application?
Thanks,
Memphodave
Message posted via http://www.sqlmonster.com
Have a look at bi-directional transactional replication.
it is covered in SP3 books online.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David Walpole via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be07a9381051493195afb35c1f13161d@.SQLMonster.c om...
> I am trying to separate two applications sharing the same database and a
few tables(like t_employee) in a phased approach. How can I best setup a
bi-directional update of this t_employee table when updated by either
application?
> Thanks,
> Memphodave
> --
> Message posted via http://www.sqlmonster.com

Bidirectional transactional replication and remote distributors

I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005

instance A lives on machine 1

instance B lives on machine 2

Instance A publishes to a transactional subscription on Instance B

Instance B does the reverse and publishes to a transactional subscription on Instance A

Instance A pushes to a distribution database on machine 2

Instance B pushes to a distribution database on machine 1

Problems Implementing Configuration

I can setup each instance as a distributor and create separate distribution databases using

sp_adddistributor and sp_adddistributiondb

I can then enable each publisher to use the correct distribution database using sp_adddistpublisher

However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55

The Distributor has not been installed correctly. Could not enable database for publishing.

If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.

It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

It seems that you can't select a remote distributor if you already have a local distributor configured.

Is there a way round this limitation?

Note that configuring the environment with a single distribution database works fine

Try calling sp_adddistpublisher on machine 1 to add machine 2 as publisher and do the same on machine 2 to add machine 1 as publisher.

Regards,

Gary

|||Check out the new Peer-Peer replication in 2005 which does exactly what you want.|||

Gary - I have tried what you suggested and got the following error as per my original post

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

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

Dinakar - thanks for the suggestion - I have been checking out peer to peer, but I am not sure if I can live with the restrictions

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

I will probably go with a single distribution database located on the publisher with the lowest load. This should be satisfactory for the requirements

Thanks

aero1

Bi-Directional replication without changing scema on subscriber

Hello - I am a newbie to Replication.
Can I setup Bi-Directional replication without introducing the guid column
on the subscriber? All the tables have Primary Keys.
Thanks.
-A
Adam - yes - columns are only added for updatable subsriptions and merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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?

BI - Test server

Hi,

I am new to BI. Can someone please tell me step by step how to setup BI test enviroment at work?

Thank you so much.

Ideally, duplicate your planned production environment on a different server and use that to test.

Setting up a test environment is dependent on a lot of variables, and this is a very vague question. If you have specific questions about SSIS in a test environment, please post them and we will try to help. If you need help on this type of general BI question, though, I'd recommend getting some training or contacting a BI company for a "jumpstart" type of engagement.

|||

Hi jwelch,

Sorry I was wrong/silly. Thanks for reply/your advise.

I will come back to you with the few questions.

Cheers

VungLiem

|||

Not wrong/silly, just looking for a little more focus in the questions so we can give you meaningful answers

Best way to setup data structure

I have an online application that works and functions like an accounting,
payroll, scheduliing and billing system. In the past, each center that
signed up would be setup in thier own database on SQL 2000. As this grows
more and more multisite organizations are using this service. It will
possible to have one organization with 2,000 centers using this service. My
question is: Should I continue setting up individual databases for each
center or should I combine the 2,000 centers for the one company into one
database and have a center key to distinguse the data. The only issue with
the mulit sites are reporting across the 2,000 or so centers. 95% of the
application is center specific the only time the data needs to be combined is
for some reports that are company wide.
Thanks,
Mark
Hi
I would do one database, but it depends on how well normalised the Db is and
what a stress test would show up.
Managing 2000 DB's becomes a nightmare.
How big ar the DB's and how big do you expect them to grow?
Regards
Mike
"Mark" wrote:

> I have an online application that works and functions like an accounting,
> payroll, scheduliing and billing system. In the past, each center that
> signed up would be setup in thier own database on SQL 2000. As this grows
> more and more multisite organizations are using this service. It will
> possible to have one organization with 2,000 centers using this service. My
> question is: Should I continue setting up individual databases for each
> center or should I combine the 2,000 centers for the one company into one
> database and have a center key to distinguse the data. The only issue with
> the mulit sites are reporting across the 2,000 or so centers. 95% of the
> application is center specific the only time the data needs to be combined is
> for some reports that are company wide.
> Thanks,
> Mark
|||The databse is normalized and where it is not, we are in the process of
fixing that now. The database size after 2 years of use is about 3 gigs - I
would imagine each database could grow to 10 gigs a piece at which time we
will get rid of some of the data. The size of the database is really
dependant on the activity of the centers.
Thanks,
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> I would do one database, but it depends on how well normalised the Db is and
> what a stress test would show up.
> Managing 2000 DB's becomes a nightmare.
> How big ar the DB's and how big do you expect them to grow?
> Regards
> Mike
> "Mark" wrote:
|||I would put everything in one DB. one of our companies has around 400
lawfirms and everything is in one database. The main thing that you have to
think about is security. In our case each lawfirm has a role and many tables
contain a field that specifies to which law firm the row belongs (row level
security). This is basically the idea but it gets more complex when you have
multiple groups with each law firm. This is based on the requirements and
something that you have to analyze). The
process might be painful but you have a lot to win as a DBA:
-amount of work for you and the developers ($$$)
-more simplified disaster recovery
-For reporting you can replicate your database on a different server and let
your clients hit the your reporting box instead of your prod.
-You will not need as many connection strings!
-Avoiding an administrative nightmare
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mark" wrote:
[vbcol=seagreen]
> The databse is normalized and where it is not, we are in the process of
> fixing that now. The database size after 2 years of use is about 3 gigs - I
> would imagine each database could grow to 10 gigs a piece at which time we
> will get rid of some of the data. The size of the database is really
> dependant on the activity of the centers.
> Thanks,
> Mark
> "Mike Epprecht (SQL MVP)" wrote:

Best way to setup data structure

I have an online application that works and functions like an accounting,
payroll, scheduliing and billing system. In the past, each center that
signed up would be setup in thier own database on SQL 2000. As this grows
more and more multisite organizations are using this service. It will
possible to have one organization with 2,000 centers using this service. My
question is: Should I continue setting up individual databases for each
center or should I combine the 2,000 centers for the one company into one
database and have a center key to distinguse the data. The only issue with
the mulit sites are reporting across the 2,000 or so centers. 95% of the
application is center specific the only time the data needs to be combined is
for some reports that are company wide.
Thanks,
MarkHi
I would do one database, but it depends on how well normalised the Db is and
what a stress test would show up.
Managing 2000 DB's becomes a nightmare.
How big ar the DB's and how big do you expect them to grow?
Regards
Mike
"Mark" wrote:
> I have an online application that works and functions like an accounting,
> payroll, scheduliing and billing system. In the past, each center that
> signed up would be setup in thier own database on SQL 2000. As this grows
> more and more multisite organizations are using this service. It will
> possible to have one organization with 2,000 centers using this service. My
> question is: Should I continue setting up individual databases for each
> center or should I combine the 2,000 centers for the one company into one
> database and have a center key to distinguse the data. The only issue with
> the mulit sites are reporting across the 2,000 or so centers. 95% of the
> application is center specific the only time the data needs to be combined is
> for some reports that are company wide.
> Thanks,
> Mark|||The databse is normalized and where it is not, we are in the process of
fixing that now. The database size after 2 years of use is about 3 gigs - I
would imagine each database could grow to 10 gigs a piece at which time we
will get rid of some of the data. The size of the database is really
dependant on the activity of the centers.
Thanks,
Mark
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> I would do one database, but it depends on how well normalised the Db is and
> what a stress test would show up.
> Managing 2000 DB's becomes a nightmare.
> How big ar the DB's and how big do you expect them to grow?
> Regards
> Mike
> "Mark" wrote:
> > I have an online application that works and functions like an accounting,
> > payroll, scheduliing and billing system. In the past, each center that
> > signed up would be setup in thier own database on SQL 2000. As this grows
> > more and more multisite organizations are using this service. It will
> > possible to have one organization with 2,000 centers using this service. My
> > question is: Should I continue setting up individual databases for each
> > center or should I combine the 2,000 centers for the one company into one
> > database and have a center key to distinguse the data. The only issue with
> > the mulit sites are reporting across the 2,000 or so centers. 95% of the
> > application is center specific the only time the data needs to be combined is
> > for some reports that are company wide.
> >
> > Thanks,
> > Mark|||I would put everything in one DB. one of our companies has around 400
lawfirms and everything is in one database. The main thing that you have to
think about is security. In our case each lawfirm has a role and many tables
contain a field that specifies to which law firm the row belongs (row level
security). This is basically the idea but it gets more complex when you have
multiple groups with each law firm. This is based on the requirements and
something that you have to analyze). The
process might be painful but you have a lot to win as a DBA:
-amount of work for you and the developers ($$$)
-more simplified disaster recovery
-For reporting you can replicate your database on a different server and let
your clients hit the your reporting box instead of your prod.
-You will not need as many connection strings!
-Avoiding an administrative nightmare
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mark" wrote:
> The databse is normalized and where it is not, we are in the process of
> fixing that now. The database size after 2 years of use is about 3 gigs - I
> would imagine each database could grow to 10 gigs a piece at which time we
> will get rid of some of the data. The size of the database is really
> dependant on the activity of the centers.
> Thanks,
> Mark
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > I would do one database, but it depends on how well normalised the Db is and
> > what a stress test would show up.
> >
> > Managing 2000 DB's becomes a nightmare.
> >
> > How big ar the DB's and how big do you expect them to grow?
> >
> > Regards
> > Mike
> >
> > "Mark" wrote:
> >
> > > I have an online application that works and functions like an accounting,
> > > payroll, scheduliing and billing system. In the past, each center that
> > > signed up would be setup in thier own database on SQL 2000. As this grows
> > > more and more multisite organizations are using this service. It will
> > > possible to have one organization with 2,000 centers using this service. My
> > > question is: Should I continue setting up individual databases for each
> > > center or should I combine the 2,000 centers for the one company into one
> > > database and have a center key to distinguse the data. The only issue with
> > > the mulit sites are reporting across the 2,000 or so centers. 95% of the
> > > application is center specific the only time the data needs to be combined is
> > > for some reports that are company wide.
> > >
> > > Thanks,
> > > Mark

Best way to setup data structure

I have an online application that works and functions like an accounting,
payroll, scheduliing and billing system. In the past, each center that
signed up would be setup in thier own database on SQL 2000. As this grows
more and more multisite organizations are using this service. It will
possible to have one organization with 2,000 centers using this service. My
question is: Should I continue setting up individual databases for each
center or should I combine the 2,000 centers for the one company into one
database and have a center key to distinguse the data. The only issue with
the mulit sites are reporting across the 2,000 or so centers. 95% of the
application is center specific the only time the data needs to be combined i
s
for some reports that are company wide.
Thanks,
MarkHi
I would do one database, but it depends on how well normalised the Db is and
what a stress test would show up.
Managing 2000 DB's becomes a nightmare.
How big ar the DB's and how big do you expect them to grow?
Regards
Mike
"Mark" wrote:

> I have an online application that works and functions like an accounting,
> payroll, scheduliing and billing system. In the past, each center that
> signed up would be setup in thier own database on SQL 2000. As this grows
> more and more multisite organizations are using this service. It will
> possible to have one organization with 2,000 centers using this service.
My
> question is: Should I continue setting up individual databases for each
> center or should I combine the 2,000 centers for the one company into one
> database and have a center key to distinguse the data. The only issue wit
h
> the mulit sites are reporting across the 2,000 or so centers. 95% of the
> application is center specific the only time the data needs to be combined
is
> for some reports that are company wide.
> Thanks,
> Mark|||The databse is normalized and where it is not, we are in the process of
fixing that now. The database size after 2 years of use is about 3 gigs - I
would imagine each database could grow to 10 gigs a piece at which time we
will get rid of some of the data. The size of the database is really
dependant on the activity of the centers.
Thanks,
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> I would do one database, but it depends on how well normalised the Db is a
nd
> what a stress test would show up.
> Managing 2000 DB's becomes a nightmare.
> How big ar the DB's and how big do you expect them to grow?
> Regards
> Mike
> "Mark" wrote:
>|||I would put everything in one DB. one of our companies has around 400
lawfirms and everything is in one database. The main thing that you have to
think about is security. In our case each lawfirm has a role and many tables
contain a field that specifies to which law firm the row belongs (row level
security). This is basically the idea but it gets more complex when you have
multiple groups with each law firm. This is based on the requirements and
something that you have to analyze). The
process might be painful but you have a lot to win as a DBA:
-amount of work for you and the developers ($$$)
-more simplified disaster recovery
-For reporting you can replicate your database on a different server and let
your clients hit the your reporting box instead of your prod.
-You will not need as many connection strings!
-Avoiding an administrative nightmare
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mark" wrote:
[vbcol=seagreen]
> The databse is normalized and where it is not, we are in the process of
> fixing that now. The database size after 2 years of use is about 3 gigs -
I
> would imagine each database could grow to 10 gigs a piece at which time we
> will get rid of some of the data. The size of the database is really
> dependant on the activity of the centers.
> Thanks,
> Mark
> "Mike Epprecht (SQL MVP)" wrote:
>

Best way to replicate Laptop users

Hi,
I have a central server witch fetches information from other local
servers. I setup merge replications so that all changes made at local
server would be replicated at central server in the night.
Now, I want all the information on central server (that is, the
combined information from all the local servers) to be dumped on the
laptop users so that they can work offline. Because of the amount of
the data I have to transfer, I can't no longer just delete all the
objects and copy over all tables any more (takes way too long.)
However, I can not setup another merge replication because the central
server is already setuped in the local to central merge replication.
The only way I could think of is to setup merge replication from local
servers to the laptop user and probably setup a job to setup the first
snapshot dump (takes around 45 minutes) in the middle of the night so
that it won't affect local server during business hour.
I am just wondering if there are better ways to do this?
Conditions:
1 - I prefer not to touch the local servers since they are vital to our
operation during regular hours (9am ~ 9pm, 7 days / week).
2 - I prefer to replication with the central server for it is a faster
server and it is not linked to any real-time operation machines (except
that it replicates with the other local servers in the middle of the
night)
3 - Snapshot replication or DTS is out of the question for the sales
table contains 2 million+ rows and growing
Any good suggestions?
Thank you in advance
Charlie Chang
[charlies224@.hotmail.com]
This might be a good case for using dynamic snapshots so the laptop users
get only the data they require. You will need a column on the tables you are
replicating to partition your data with.
I would use WSM (Windows Synchronization Manager) to manage the pull.
Depending on your data flow you might be able to publish from the local
servers.
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
<charlies224@.hotmail.com> wrote in message
news:1109629058.527708.180340@.l41g2000cwc.googlegr oups.com...
> Hi,
> I have a central server witch fetches information from other local
> servers. I setup merge replications so that all changes made at local
> server would be replicated at central server in the night.
> Now, I want all the information on central server (that is, the
> combined information from all the local servers) to be dumped on the
> laptop users so that they can work offline. Because of the amount of
> the data I have to transfer, I can't no longer just delete all the
> objects and copy over all tables any more (takes way too long.)
> However, I can not setup another merge replication because the central
> server is already setuped in the local to central merge replication.
> The only way I could think of is to setup merge replication from local
> servers to the laptop user and probably setup a job to setup the first
> snapshot dump (takes around 45 minutes) in the middle of the night so
> that it won't affect local server during business hour.
> I am just wondering if there are better ways to do this?
> Conditions:
> 1 - I prefer not to touch the local servers since they are vital to our
> operation during regular hours (9am ~ 9pm, 7 days / week).
> 2 - I prefer to replication with the central server for it is a faster
> server and it is not linked to any real-time operation machines (except
> that it replicates with the other local servers in the middle of the
> night)
> 3 - Snapshot replication or DTS is out of the question for the sales
> table contains 2 million+ rows and growing
>
> Any good suggestions?
> Thank you in advance
> Charlie Chang
> [charlies224@.hotmail.com]
>

Best way to move SQL 6.5 to new hardware

Hi,
Can anyone tell me the best way to moving MS SQL 6.5 System to new
Hardware Box?
I already setup new SQL Server using different computer name, just want
to copy all data and security info to new one.
Many thanks in advance.
Regards,
Chris Lee
Chris,
there's a huge amount of things to take into account, but when I did it I
found this article very helpful:
http://www.microsoft.com/technet/pro...y/sqlugrd.mspx
HTH,
Paul Ibison
|||Dear Paul,
Thanks for your reply, but the article apply to SQL 6.5 to SQL 6.5 upgarde?
Regards,
Chris Lee
|||Hi,
Did you installed the SQL Server with the same directory structure as actual
(Old) SQL server. If yes then:-
1. Apply the same service pack in the new server (Probably sp5a + post
update 5a)
2. Stop the SQL server in old server and new server
3. Copy all the DAT (Including system databases) files from (
drive:\mssql\data) old server to
new server
4. Start SQL server in new server
5. Using ISQLw login to SQL Server and execute the below script
sp_dropserver 'old_server_name',
go
sp_addserver 'new_server_name', 'local'
6. Stop and Start the SQL Server service
Thanks
Hari
MCDBA
"Chris Lee" <c_h_r_i_s_l_e_e_@.h_o_t_m_a_i_l_._c_o_m> wrote in message
news:ca93g0$2pf8@.imsp212.netvigator.com...
> Hi,
> Can anyone tell me the best way to moving MS SQL 6.5 System to new
> Hardware Box?
> I already setup new SQL Server using different computer name, just want
> to copy all data and security info to new one.
> Many thanks in advance.
> Regards,
> Chris Lee
|||Apologies - just assumed you were upgrading. Please refer
to Hari's post then.
Regards,
Paul Ibison
|||Thanks Hari,
It's work!!! Great Thanks!
Regards,
Chris Lee

Best way to move SQL 6.5 to new hardware

Hi,
Can anyone tell me the best way to moving MS SQL 6.5 System to new
Hardware Box?
I already setup new SQL Server using different computer name, just want
to copy all data and security info to new one.
Many thanks in advance.
Regards,
Chris LeeChris,
there's a huge amount of things to take into account, but when I did it I
found this article very helpful:
http://www.microsoft.com/technet/pr...oy/sqlugrd.mspx
HTH,
Paul Ibison|||Dear Paul,
Thanks for your reply, but the article apply to SQL 6.5 to SQL 6.5 upgarde?
Regards,
Chris Lee|||Hi,
Did you installed the SQL Server with the same directory structure as actual
(Old) SQL server. If yes then:-
1. Apply the same service pack in the new server (Probably sp5a + post
update 5a)
2. Stop the SQL server in old server and new server
3. Copy all the DAT (Including system databases) files from (
drive:\mssql\data) old server to
new server
4. Start SQL server in new server
5. Using ISQLw login to SQL Server and execute the below script
sp_dropserver 'old_server_name',
go
sp_addserver 'new_server_name', 'local'
6. Stop and Start the SQL Server service
Thanks
Hari
MCDBA
"Chris Lee" <c_h_r_i_s_l_e_e_@.h_o_t_m_a_i_l_._c_o_m> wrote in message
news:ca93g0$2pf8@.imsp212.netvigator.com...
> Hi,
> Can anyone tell me the best way to moving MS SQL 6.5 System to new
> hardware Box?
> I already setup new SQL Server using different computer name, just want
> to copy all data and security info to new one.
> Many thanks in advance.
> Regards,
> Chris Lee|||Apologies - just assumed you were upgrading. Please refer
to Hari's post then.
Regards,
Paul Ibison|||Thanks Hari,
It's work!!! Great Thanks!
Regards,
Chris Lee

Best way to move SQL 6.5 to new hardware

Hi,
Can anyone tell me the best way to moving MS SQL 6.5 System to new
Hardware Box?
I already setup new SQL Server using different computer name, just want
to copy all data and security info to new one.
Many thanks in advance.
Regards,
Chris LeeChris,
there's a huge amount of things to take into account, but when I did it I
found this article very helpful:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
HTH,
Paul Ibison|||Dear Paul,
Thanks for your reply, but the article apply to SQL 6.5 to SQL 6.5 upgarde?
Regards,
Chris Lee|||Hi,
Did you installed the SQL Server with the same directory structure as actual
(Old) SQL server. If yes then:-
1. Apply the same service pack in the new server (Probably sp5a + post
update 5a)
2. Stop the SQL server in old server and new server
3. Copy all the DAT (Including system databases) files from (
drive:\mssql\data) old server to
new server
4. Start SQL server in new server
5. Using ISQLw login to SQL Server and execute the below script
sp_dropserver 'old_server_name',
go
sp_addserver 'new_server_name', 'local'
6. Stop and Start the SQL Server service
Thanks
Hari
MCDBA
"Chris Lee" <c_h_r_i_s_l_e_e_@.h_o_t_m_a_i_l_._c_o_m> wrote in message
news:ca93g0$2pf8@.imsp212.netvigator.com...
> Hi,
> Can anyone tell me the best way to moving MS SQL 6.5 System to new
> Hardware Box?
> I already setup new SQL Server using different computer name, just want
> to copy all data and security info to new one.
> Many thanks in advance.
> Regards,
> Chris Lee|||Apologies - just assumed you were upgrading. Please refer
to Hari's post then.
Regards,
Paul Ibison|||Thanks Hari,
It's work!!! Great Thanks!
Regards,
Chris Lee

Best way to install data files?

Warning beginner here.
I posted this in the setup news group but haven't received and replies, so
perphaps someone here can help me.
I am developing a small ASP.NET web application that will use an SQL Server
2000 database. The database will start with 1,000 records in a main table
and about 100,000 records in a related table. How much this database will
grow is unknown.
I have one desktop machine that I am using for testing. It has one hard
drive, and nothing special about it.
Another test machine will pretend to be a production server. It is a real
server. It has the operating (Win Server 2003) system on a RAID 1 (2 drives)
setup. It also has 6 other drives set up as a RAID 5.
Should SQL Server be installed on the RAID 1, and then install the data
files only to the RAID 5? OR Should SQL Server and the data files be
installed on the RAID 5 drive?
The reason I ask is... From prior experience with Access I learned that
although attached data files can be worked with, they often required much
more code.
So... What is the best way to do this? And... why?
TIA.
With the disk setup that server has, install the SQL Server executables on
the RAID 1, and the user database on the RAID 5. You can create the master,
model and msdb system databases on the RAID 1 as well, and the tempdb on
RAID 1 as well, if there is enough space.
Jacco Schalkwijk
SQL Server MVP
"kvr901" <kvr901@.discussions.microsoft.com> wrote in message
news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
> Warning beginner here.
> I posted this in the setup news group but haven't received and replies, so
> perphaps someone here can help me.
> I am developing a small ASP.NET web application that will use an SQL
> Server
> 2000 database. The database will start with 1,000 records in a main table
> and about 100,000 records in a related table. How much this database will
> grow is unknown.
> I have one desktop machine that I am using for testing. It has one hard
> drive, and nothing special about it.
> Another test machine will pretend to be a production server. It is a real
> server. It has the operating (Win Server 2003) system on a RAID 1 (2
> drives)
> setup. It also has 6 other drives set up as a RAID 5.
> Should SQL Server be installed on the RAID 1, and then install the data
> files only to the RAID 5? OR Should SQL Server and the data files be
> installed on the RAID 5 drive?
> The reason I ask is... From prior experience with Access I learned that
> although attached data files can be worked with, they often required much
> more code.
> So... What is the best way to do this? And... why?
> TIA.
>
|||Thank you for your reply.
Another question:
**IF** some time in the future I decide to distribute the application
including the distributable version of SQL Server (I believe it is called the
"MSDE") would the installation procedure be the same?
In earlier versions of Access there was a considerable learning curve
(coding) to use attached Access secured "backend" databases, and I am
wondering if I should expect similar hurdles with the distributable version
of SQL Server.
Thank you.
"Jacco Schalkwijk" wrote:

> With the disk setup that server has, install the SQL Server executables on
> the RAID 1, and the user database on the RAID 5. You can create the master,
> model and msdb system databases on the RAID 1 as well, and the tempdb on
> RAID 1 as well, if there is enough space.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "kvr901" <kvr901@.discussions.microsoft.com> wrote in message
> news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
>
>
|||kvr901 wrote:[vbcol=seagreen]
> Thank you for your reply.
> Another question:
> **IF** some time in the future I decide to distribute the application
> including the distributable version of SQL Server (I believe it is called the
> "MSDE") would the installation procedure be the same?
> In earlier versions of Access there was a considerable learning curve
> (coding) to use attached Access secured "backend" databases, and I am
> wondering if I should expect similar hurdles with the distributable version
> of SQL Server.
> Thank you.
>
> "Jacco Schalkwijk" wrote:
>
I am not sure I understand your concern about attached files. Once a
database is mounted by the server accessing is no different than any
other database on the server, regardless of the location of the actual
physical data file.
As to installing your database on a customers site. The issues around
RAID are more data integrity and performance related. Whatever their
configuration is will be dependent on the number of users and their
pocket books. But the system will work on any platform that supports SQL
Server.

Best way to install data files?

Warning beginner here.
I posted this in the setup news group but haven't received and replies, so
perphaps someone here can help me.
I am developing a small ASP.NET web application that will use an SQL Server
2000 database. The database will start with 1,000 records in a main table
and about 100,000 records in a related table. How much this database will
grow is unknown.
I have one desktop machine that I am using for testing. It has one hard
drive, and nothing special about it.
Another test machine will pretend to be a production server. It is a real
server. It has the operating (Win Server 2003) system on a RAID 1 (2 drives
)
setup. It also has 6 other drives set up as a RAID 5.
Should SQL Server be installed on the RAID 1, and then install the data
files only to the RAID 5? OR Should SQL Server and the data files be
installed on the RAID 5 drive?
The reason I ask is... From prior experience with Access I learned that
although attached data files can be worked with, they often required much
more code.
So... What is the best way to do this? And... why?
TIA.With the disk setup that server has, install the SQL Server executables on
the RAID 1, and the user database on the RAID 5. You can create the master,
model and msdb system databases on the RAID 1 as well, and the tempdb on
RAID 1 as well, if there is enough space.
Jacco Schalkwijk
SQL Server MVP
"kvr901" <kvr901@.discussions.microsoft.com> wrote in message
news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
> Warning beginner here.
> I posted this in the setup news group but haven't received and replies, so
> perphaps someone here can help me.
> I am developing a small ASP.NET web application that will use an SQL
> Server
> 2000 database. The database will start with 1,000 records in a main table
> and about 100,000 records in a related table. How much this database will
> grow is unknown.
> I have one desktop machine that I am using for testing. It has one hard
> drive, and nothing special about it.
> Another test machine will pretend to be a production server. It is a real
> server. It has the operating (Win Server 2003) system on a RAID 1 (2
> drives)
> setup. It also has 6 other drives set up as a RAID 5.
> Should SQL Server be installed on the RAID 1, and then install the data
> files only to the RAID 5? OR Should SQL Server and the data files be
> installed on the RAID 5 drive?
> The reason I ask is... From prior experience with Access I learned that
> although attached data files can be worked with, they often required much
> more code.
> So... What is the best way to do this? And... why?
> TIA.
>|||Thank you for your reply.
Another question:
**IF** some time in the future I decide to distribute the application
including the distributable version of SQL Server (I believe it is called th
e
"MSDE") would the installation procedure be the same?
In earlier versions of Access there was a considerable learning curve
(coding) to use attached Access secured "backend" databases, and I am
wondering if I should expect similar hurdles with the distributable version
of SQL Server.
Thank you.
"Jacco Schalkwijk" wrote:

> With the disk setup that server has, install the SQL Server executables on
> the RAID 1, and the user database on the RAID 5. You can create the master
,
> model and msdb system databases on the RAID 1 as well, and the tempdb on
> RAID 1 as well, if there is enough space.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "kvr901" <kvr901@.discussions.microsoft.com> wrote in message
> news:E45F9F10-8B51-4644-A239-9A4F674BBFFD@.microsoft.com...
>
>|||kvr901 wrote:[vbcol=seagreen]
> Thank you for your reply.
> Another question:
> **IF** some time in the future I decide to distribute the application
> including the distributable version of SQL Server (I believe it is called
the
> "MSDE") would the installation procedure be the same?
> In earlier versions of Access there was a considerable learning curve
> (coding) to use attached Access secured "backend" databases, and I am
> wondering if I should expect similar hurdles with the distributable versio
n
> of SQL Server.
> Thank you.
>
> "Jacco Schalkwijk" wrote:
>
I am not sure I understand your concern about attached files. Once a
database is mounted by the server accessing is no different than any
other database on the server, regardless of the location of the actual
physical data file.
As to installing your database on a customers site. The issues around
RAID are more data integrity and performance related. Whatever their
configuration is will be dependent on the number of users and their
pocket books. But the system will work on any platform that supports SQL
Server.

best way to audit activity

I need setup an audit trail on all CRUD that happens on a SQL 2000 database.
I don't want to enable C2 auditing since I think that would be overkill.
Creating a Profiler template and tuning it to the exact info I want seems th
e
right approach, but using the profiler is not ideal since it cannot survive
a
reboot without having to manually restart the trace. I think what I need to
do is to make a stored procedure that uses the sp_trace* stored procs to
start a trace and add at .bat file to the system startup that triggers the
stored proc to start running.
The problem is that I can't seem to find the documentation I need to l
understand how to use sp_trace_setevent. Also, it seems like it would be a
lot easier if I could just create a SQL Profiler template and have a stored
procedure just use the template. I think I've run into some mention that thi
s
is possible, but nothing more than that.
Is it possible to just use a profiler template? If so, I need code samples
to get me started, and if not, then I need complete documentation on
sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
someone point me in the right direction here?The easiest thing is to get your trace set up exactly as you want it in
profiler and then under the File->Export menu export it as a SQL Script. It
will generate a .sql file with all the commands you need to generate the
equivalent server-side trace.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>I need setup an audit trail on all CRUD that happens on a SQL 2000
>database.
> I don't want to enable C2 auditing since I think that would be overkill.
> Creating a Profiler template and tuning it to the exact info I want seems
> the
> right approach, but using the profiler is not ideal since it cannot
> survive a
> reboot without having to manually restart the trace. I think what I need
> to
> do is to make a stored procedure that uses the sp_trace* stored procs to
> start a trace and add at .bat file to the system startup that triggers the
> stored proc to start running.
> The problem is that I can't seem to find the documentation I need to l
> understand how to use sp_trace_setevent. Also, it seems like it would be a
> lot easier if I could just create a SQL Profiler template and have a
> stored
> procedure just use the template. I think I've run into some mention that
> this
> is possible, but nothing more than that.
> Is it possible to just use a profiler template? If so, I need code samples
> to get me started, and if not, then I need complete documentation on
> sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
> someone point me in the right direction here?|||1. Open Profiler.
2. Create you trace
3. Under the file menu, there is an option to save the trace which will
generate a T-SQL script with all of the commands that you need.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>I need setup an audit trail on all CRUD that happens on a SQL 2000
>database.
> I don't want to enable C2 auditing since I think that would be overkill.
> Creating a Profiler template and tuning it to the exact info I want seems
> the
> right approach, but using the profiler is not ideal since it cannot
> survive a
> reboot without having to manually restart the trace. I think what I need
> to
> do is to make a stored procedure that uses the sp_trace* stored procs to
> start a trace and add at .bat file to the system startup that triggers the
> stored proc to start running.
> The problem is that I can't seem to find the documentation I need to l
> understand how to use sp_trace_setevent. Also, it seems like it would be a
> lot easier if I could just create a SQL Profiler template and have a
> stored
> procedure just use the template. I think I've run into some mention that
> this
> is possible, but nothing more than that.
> Is it possible to just use a profiler template? If so, I need code samples
> to get me started, and if not, then I need complete documentation on
> sp_trace_setevent params and maybe all of the other sp_trace* procs. Can
> someone point me in the right direction here?|||Oh I see. I had overlooked that option. It was greyed out until I was
actually running the trace. One thing I'd do with the script is have it
append the trace file name with the date (no problem). This would mean that
I'll have to make the trace run until I tell it to stop (let's say at
24:59:59) and make my bat file start it again at (at 1:00:00). Starting it
with a bat file seems easy enough, but how exactly would I go about stopping
the trace? Also, since I want the trace to run on all databases on the serve
r
instance, where do I put the strored proc, in the master database?
"Roger Wolter[MSFT]" wrote:

> The easiest thing is to get your trace set up exactly as you want it in
> profiler and then under the File->Export menu export it as a SQL Script.
It
> will generate a .sql file with all the commands you need to generate the
> equivalent server-side trace.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
> news:63773881-DC9F-4E1D-A417-7FEE4D794131@.microsoft.com...
>
>|||You can specify a stop time in sp_create_trace so it stops when you want it
to. sp_trace_set_status can also be used to start or stop a trace. I would
use an agent job or a Service Broker Conversation Timer to start the trace.
Keep in mind that starting and stopping the trace runs the risk of missing
commands so just setting the file size to a reasonable size and letting
trace create new files when it needs to is probably safer. If you size it
to get about a days worth of data, the timestamp on the files should let you
find the data you want. You can use fn_trace_gettable to load the file you
want into a table to find the statements you're looking for.
Traces are global so it doesn't matter where you run it from.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"archuleta37" <archuleta37@.discussions.microsoft.com> wrote in message
news:5CF44DFC-623E-4089-9034-B58A0869F811@.microsoft.com...[vbcol=seagreen]
> Oh I see. I had overlooked that option. It was greyed out until I was
> actually running the trace. One thing I'd do with the script is have it
> append the trace file name with the date (no problem). This would mean
> that
> I'll have to make the trace run until I tell it to stop (let's say at
> 24:59:59) and make my bat file start it again at (at 1:00:00). Starting it
> with a bat file seems easy enough, but how exactly would I go about
> stopping
> the trace? Also, since I want the trace to run on all databases on the
> server
> instance, where do I put the strored proc, in the master database?
>
> "Roger Wolter[MSFT]" wrote:
>