Advice Needed

I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the wends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the wend
s. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:

> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip wends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the wends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:

> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip wends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>sql

Advice Needed

I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.
>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.
|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:

> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>
|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:

> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>

Advice Needed

I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs t
o allow for multiple times per day and if the task should run on the weekend
s. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
.);
Just fill in the schedule for each task.
[vbcol=seagreen]
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:

> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
>
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...[vbcol=seagreen]
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule an
d
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:

> Not sure myself becuase of my limited experience. I do know that I need t
he
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
>
>

Advice Needed

I need to set up a table to define times when tasks will run. It needs to
allow for multiple times per day and if the task should run on the weekends.
Once the task has run once for the specified service window for that time of
day I need to prevent the task from running again. I do not have a lot of
SQL experience and was wondering the best way to set up the table structure
might be. Any thoughts, examples or links would be greatly appreciated.
Thank you.>> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
CREATE TABLE TaskSchedule
(task_name CHAR(20) NOT NULL,
task_time DATETIME NOT NULL,
PRIMARY KEY (task_name, task_time),
..);
Just fill in the schedule for each task.
>> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
events. What were you thinking about doing? We do not compute things
in SQL; we put them in tables and do joins.|||I'm wondering why you need a table. If the tasks are SQL Scripts, then just
run a scheduled DTS job or Store Procedure. You can create jobs to run at
the specific times and they would only run once. Plus you can have failure
emails if the job fails and will contact you.
"--CELKO--" wrote:
> >> I need to set up a table to define times when tasks will run. It needs to allow for multiple times per day and if the task should run on the weekends. <<
> CREATE TABLE TaskSchedule
> (task_name CHAR(20) NOT NULL,
> task_time DATETIME NOT NULL,
> PRIMARY KEY (task_name, task_time),
> ..);
> Just fill in the schedule for each task.
> >> Once the task has run once for the specified service window for that time of day I need to prevent the task from running again. <<
> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> events. What were you thinking about doing? We do not compute things
> in SQL; we put them in tables and do joins.
>|||Not sure myself becuase of my limited experience. I do know that I need the
ability to have my sproc run more then once in a day, possibly different
times on different days, and the ability to skip weekends. Can I do all
this using a scheduled DTS job or sproc? Thank you.
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> I'm wondering why you need a table. If the tasks are SQL Scripts, then
> just
> run a scheduled DTS job or Store Procedure. You can create jobs to run at
> the specific times and they would only run once. Plus you can have
> failure
> emails if the job fails and will contact you.
> "--CELKO--" wrote:
>> >> I need to set up a table to define times when tasks will run. It
>> >> needs to allow for multiple times per day and if the task should run
>> >> on the weekends. <<
>> CREATE TABLE TaskSchedule
>> (task_name CHAR(20) NOT NULL,
>> task_time DATETIME NOT NULL,
>> PRIMARY KEY (task_name, task_time),
>> ..);
>> Just fill in the schedule for each task.
>> >> Once the task has run once for the specified service window for that
>> >> time of day I need to prevent the task from running again. <<
>> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
>> events. What were you thinking about doing? We do not compute things
>> in SQL; we put them in tables and do joins.
>>|||Yes you can run the job except for the weekends. In Enterprise Manager, go
to the Managment Folder, Expand SQL Server Agent, and you will see the jobs
section. Create a new job and in the Schedules tab create a new schedule and
you will see the options you can do. Good luck.
Darren
"Code Boy" wrote:
> Not sure myself becuase of my limited experience. I do know that I need the
> ability to have my sproc run more then once in a day, possibly different
> times on different days, and the ability to skip weekends. Can I do all
> this using a scheduled DTS job or sproc? Thank you.
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:9AE28789-D43D-422F-B185-D682C5BF289A@.microsoft.com...
> > I'm wondering why you need a table. If the tasks are SQL Scripts, then
> > just
> > run a scheduled DTS job or Store Procedure. You can create jobs to run at
> > the specific times and they would only run once. Plus you can have
> > failure
> > emails if the job fails and will contact you.
> >
> > "--CELKO--" wrote:
> >
> >> >> I need to set up a table to define times when tasks will run. It
> >> >> needs to allow for multiple times per day and if the task should run
> >> >> on the weekends. <<
> >>
> >> CREATE TABLE TaskSchedule
> >> (task_name CHAR(20) NOT NULL,
> >> task_time DATETIME NOT NULL,
> >> PRIMARY KEY (task_name, task_time),
> >> ..);
> >>
> >> Just fill in the schedule for each task.
> >>
> >> >> Once the task has run once for the specified service window for that
> >> >> time of day I need to prevent the task from running again. <<
> >>
> >> Use a query with "task_time >= CURRENT_TIMESTAMP" so you ignore past
> >> events. What were you thinking about doing? We do not compute things
> >> in SQL; we put them in tables and do joins.
> >>
> >>
>
>sql

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advanced
Hi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database may
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overload
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:

> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>
|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:

>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.
|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenario
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>
|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
--
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advancedHi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database ma
y
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overloa
d
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:

> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the databas
e.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:

>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database
.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenari
o
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advice about database deployment

Hello All,
I am writing a database application that will serve multiple companies,
each company contains users that will store/retrieve data from the database.
I am in the process of deciding whether I should have one big database
that stores data for all users of all companies or have a separate
database for each company.
What would be the advantage/disadvantage of selecting one over the other
with the following considerations?
Considerations:
--
1. failover support
2. performance
3. load balancing via adding multiple database servers
4. clustering
5. backup (full and incremental)
6. database upgrade (eg. adding more tables and relationships, modifying
tables and relationships)
7. special equipment considerations
Any advised would be greatly appreciated.
Thanks in advancedHi
Both methods have their advantages and disadvantages so you will have to
weigh up the pros and cons to make a discission. Having a single database may
be ruled out because of security reasons (in fact having databases on the
same instance may be ruled out!) , but it will ease the management and
upgrades. Having single databases will allow you to tailor the databases to
specific requirements and (say) adjust intensive tasks so they don't overload
the system e.g. spread out the backups and other maintenance tasks. Having
separate databases would potentially limit the scope of any mistake (e.g. if
someone managed to take an exclusive lock on a table the impact would be
within a single company not other possibly unrelated companies who could sue
you!).
Hardware clustering should not be affected by either configuration, although
if you are using SQL 2005 and database mirroring you could fail over one
different databases separately and mix the primary instances in similar way
to having multiple instance on a clustered server.
HTH
John
"buzlite@.sympatico.ca" wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced
>|||Hi
In addition to John's reply ,I'd ask you ,how big will be databases? Have
you considered to install SQL Server 2005 Express Edition on the company's
site?
It depends on your business requirement , thus there is no common scenario
<buzlite@.sympatico.ca> wrote in message
news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced|||On Fri, 17 Nov 2006 08:46:13 -0500, "buzlite@.sympatico.ca"
<buzlite@.sympatico.ca> wrote:
>Hello All,
>I am writing a database application that will serve multiple companies,
>each company contains users that will store/retrieve data from the database.
>I am in the process of deciding whether I should have one big database
>that stores data for all users of all companies or have a separate
>database for each company.
>What would be the advantage/disadvantage of selecting one over the other
>with the following considerations?
>Considerations:
>--
>1. failover support
>2. performance
>3. load balancing via adding multiple database servers
>4. clustering
>5. backup (full and incremental)
>6. database upgrade (eg. adding more tables and relationships, modifying
>tables and relationships)
>7. special equipment considerations
>Any advised would be greatly appreciated.
>Thanks in advanced
* Impact of company-X on the performance of company-Y's operations.
Given a choice like this, perhaps the answer is: both.
Write it for one database for better sharing, it's probably easier to
un-share later, than to add the sharing. Easier to do any
consolidations, when they're on one database, of course.
Then you can deploy it all on one system/instance or on N
systems/instances, if one of the big companies decides they want their
own servers after all.
J.|||I would say that the data and amount of data from one company could be
pretty large. Given that images can be uploaded to the database in
addition to text.
Given a previous reply to this posting and the fact that large amounts
of data can be produce by any or all given companies, the performance
degradation related factor is indeed a point that needs to be considered.
Thanks
Uri Dimant wrote:
> Hi
> In addition to John's reply ,I'd ask you ,how big will be databases? Have
> you considered to install SQL Server 2005 Express Edition on the company's
> site?
> It depends on your business requirement , thus there is no common scenario
>
>
> <buzlite@.sympatico.ca> wrote in message
> news:%23Atf%236kCHHA.3916@.TK2MSFTNGP06.phx.gbl...
>> Hello All,
>> I am writing a database application that will serve multiple companies,
>> each company contains users that will store/retrieve data from the
>> database.
>> I am in the process of deciding whether I should have one big database
>> that stores data for all users of all companies or have a separate
>> database for each company.
>> What would be the advantage/disadvantage of selecting one over the other
>> with the following considerations?
>> Considerations:
>> --
>> 1. failover support
>> 2. performance
>> 3. load balancing via adding multiple database servers
>> 4. clustering
>> 5. backup (full and incremental)
>> 6. database upgrade (eg. adding more tables and relationships, modifying
>> tables and relationships)
>> 7. special equipment considerations
>> Any advised would be greatly appreciated.
>> Thanks in advanced
>|||To Add to the original post, what would the impact of using one database
for all companies and one database instance for each company as it
relates to the following:
Full backups
Transaction log backups
Rebuilding indexes
Integrity Check
buzlite@.sympatico.ca wrote:
> Hello All,
> I am writing a database application that will serve multiple companies,
> each company contains users that will store/retrieve data from the
> database.
> I am in the process of deciding whether I should have one big database
> that stores data for all users of all companies or have a separate
> database for each company.
> What would be the advantage/disadvantage of selecting one over the other
> with the following considerations?
> Considerations:
> --
> 1. failover support
> 2. performance
> 3. load balancing via adding multiple database servers
> 4. clustering
> 5. backup (full and incremental)
> 6. database upgrade (eg. adding more tables and relationships, modifying
> tables and relationships)
> 7. special equipment considerations
> Any advised would be greatly appreciated.
> Thanks in advanced

Advantages of Multiple Instances

Where can I find advantages of Multiple Instances on a single Server.

Instances are fairly well isolated from one another. They can run under different service accounts, store data by default on separate disks, etc. In 30 second of looking through BOL I couldn't find a good summary description of instances but I bet it's in there somewhere. Do you have a more specific question or are you looking for general info? Are you asking about the SQL Server Engine in particular, or other components?

Paul

|||

On intranet we are having some applications, in particular time everyday we are uploading bulk data onto a database. Due to this other applications are unable to access the database server OR it is very slow. In this case is there any advantage of using Multiple Instances ?

Here databases are sharing some of the data each other.

|||

I'm moving this thread to the database engine forum for expert comment. Are the bulk load and other applications accessing the same table or different tables? Is the bulk load consuming a large percentage of CPU or disk throughput?

Paul

|||Use local snapshot replication (pull).

Advanced Data Shaping - Multiple Relate clause

Hello,

I'm using a shape query, but instead of using a simple clause "RELATE
field1 to field2" (relates the parent to the child), i wan't to use 2
relates. somthing like "RELATE field1 to field2 AND field3 to field4".

I want to receive in the children RS only the records who apply both
conditions.

How do i do that ?

Thanks !(doar123@.gmail.com) writes:
> I'm using a shape query, but instead of using a simple clause "RELATE
> field1 to field2" (relates the parent to the child), i wan't to use 2
> relates. somthing like "RELATE field1 to field2 AND field3 to field4".
> I want to receive in the children RS only the records who apply both
> conditions.
> How do i do that ?

I suspect that a newsgroup on ADO is better fitted for this question.

Or try Google. Entering SHAPE APPEND RELATE led me to
http://support.microsoft.com/default.aspx?scid=189657 which a
appears to have the answer you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I havn't tryed. My idea: Create a field in the parent rs wich contains
both field1 and field2. For example:
Fieldx = cast(field1 as char(10)) + cast(field2 as char(10))
Do this in the child-rs, too and relate them with the Fieldx

Michael

www.zankl-it.de

ADO.NET parameterized query security

I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.

Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?

From your description, you are relying on ownership chaining to access tables only from SPs; this is possible only because the SP owner is the same as the underlying tables and the security checks are bypassed; but sqlcommand will not be able to use any chaining directly.

In any case, we would like to understand your needs in order to give better advice as well as to understand our customer needs. For example, is the reason behind not granting permissions directly on the table to protect from ad-hoc queries, or to help in managing permissions? If it is for managing permissions, what may be the obstacle from using the existing permission model?

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

You don′t need to grant permissions on the tables as long as the owner of the tables are the same as those for the stored procedure. This is called owner ship chaining, there is more to read about that in the BOL. Don′t breaking the ownership chain means that the permission is checked once at the procedure level. SQL Server assumes then lateron during the access of the table that if the grantee has access to the proc and the grantor created the stored procedure accessing the base tables th grantor also wanted the grantee to access the base tables. Permissions are not checked twice then. If the ownership chain is broken (Another owner of the base tables than the stored procedure) permissions are checked for every underlying base object. Using SQL Server 2005 you can also use impersonation within your stored procedures, acessing data / base tables using the WiTH EXECUTE AS syntax.

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

Thank you all for your quick response and comments!

The reason to avoid granting access directly on the tables is to protect from ad-hoc queries, if an unauthorized user gains access through the login used by the website. To minimize the damage if that were to happen, we want this login to have only minimal rights to the database.

From a wider perspective, I normally prefer using stored procedures, but sometimes we need the flexibility of building a parameterized query in the Web application. I would like to find a way of doing that without giving up the security advantage of using stored procedures. What is the best way to accomplish this?

|||

I would recommend following Jens’ suggestion and use either EXECUTE AS (or digital signatures) in order to change the execution context before accessing the tables. Here are a few good starting points in BOL for this topic:

· Context switching (http://msdn2.microsoft.com/en-us/library/ms188268.aspx)

· Module signing (http://msdn2.microsoft.com/en-us/library/ms345102.aspx)

If you decide to use this mechanism, I would also like to strongly recommend following the least privilege principle. For example, if for this application the application (impersonated) context only needs to have SELECT on a couple of tables, make sure that the permissions are limited only to the proper tables.

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

ADO Performance question

Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
PushkarHi
Why not just calling a stored procedures that does the job?
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uA7uMRk2FHA.472@.TK
2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
Pushkar|||If you are inserting 100,000+ records, then calling a Recordset.AddNew for e
ach record will be very slow.
I once wrote an ETL application in VB6.0/ADO2.5 that performed some rather c
omplex data transformations on millions of records per day that were derived
from the mainframe and MS Excel. I would run the app on the local workstati
on, appending each record to a tab delimited text file. When this step compl
eted, I would then bulk copy (BCP.EXE) the resulting file into SQL Server. T
he difference in runtime per batch was a couple of hours vs. 20 minutes. Ano
ther advantage to this is you can run the application on multiple PCs with n
o load on the database server except for the bulk copy operations.
You may also want to try posting to microsoft.public.data.ado
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uA7uMRk2FHA.472@.TK
2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a table
.
Currently I am combining all my insert queries and passing as command text t
o ADO command object.
There is one more way by opening a recordset and call AddNew on recordset mu
ltiple times and then batch update.
But I am not sure which method is better than other and which approach shoul
d I use.
Thanks in advance.
Pushkar|||Batching is generally faster. But you are moving a lot of data across
the network. As Uri mentions, why not use a stored procedure instead
(even if this implies a few calls to the procedure). If you are dealing
with thousands of rows, this might perform better using a bulk insert or
running locally on the server.
David Gugick
Quest Software
www.quest.com
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:uA7uMRk2FHA.472@.TK2MSFTNGP15.phx.gbl...
Hi,
I am writing a code which is actually performing multiple inserts on a
table.
Currently I am combining all my insert queries and passing as command
text to ADO command object.
There is one more way by opening a recordset and call AddNew on
recordset multiple times and then batch update.
But I am not sure which method is better than other and which approach
should I use.
Thanks in advance.
Pushkar

ADO Integrated Security Pass Through

I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Is it safe enough to use Integrated Security via the connection string. If
that succeeds, read the windows user name and check that against an internal
username?
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
Any comments or suggestions would be great.First key point: By using Windows authentication, you do NOT have to have a
USERS table. In SQL Server, you assign all permissions to the Windows login
or the Windows network group. (It makes life so much easier for the DBA.)
Windows Integrated Security (for SQL 2000) is far superior to SQL
authentication, and far better than trying to keep a USERS table up to date.
In SQL Server, use the SYSTEM_USER system function to retrieve the users
login name (in the form of [domain\username].)
Example:
SELECT SYSTEM_USER
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uJVfx3ntGHA.2392@.TK2MSFTNGP05.phx.gbl...
>I am working on making my application inherit the workstation login user
>information so that users aren't presented with multiple logins.
> Is it safe enough to use Integrated Security via the connection string. If
> that succeeds, read the windows user name and check that against an
> internal username?
> Does anyone have any experience linking internal application user
> management (ie: USERS table) with windows authentication?
> Any comments or suggestions would be great.
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Mmz2potGHA.324@.TK2MSFTNGP06.phx.gbl...
> First key point: By using Windows authentication, you do NOT have to have
> a USERS table. In SQL Server, you assign all permissions to the Windows
> login or the Windows network group. (It makes life so much easier for the
> DBA.)
> Windows Integrated Security (for SQL 2000) is far superior to SQL
> authentication, and far better than trying to keep a USERS table up to
> date.
> In SQL Server, use the SYSTEM_USER system function to retrieve the users
> login name (in the form of [domain\username].)
> Example:
> SELECT SYSTEM_USER
> --
Thanks Arnie. That call is very helpful.
However, I have access rights that a specific to my application such as menu
options, reports, specific actions, etc. The application was developed 8
years ago and supported multiple database platforms. We have moved to only
supporting MS SQL Server 7, 2000 and 2005.
What's the best way to link these together since DBAs wouldn't be able to
assign my application specific access rights via MS SQL Server Management
Studio/Enterprise Manager?

ADO Integrated Security Pass Through

I am working on making my application inherit the workstation login user
information so that users aren't presented with multiple logins.
Is it safe enough to use Integrated Security via the connection string. If
that succeeds, read the windows user name and check that against an internal
username?
Does anyone have any experience linking internal application user management
(ie: USERS table) with windows authentication?
Any comments or suggestions would be great.First key point: By using Windows authentication, you do NOT have to have a
USERS table. In SQL Server, you assign all permissions to the Windows login
or the Windows network group. (It makes life so much easier for the DBA.)
Windows Integrated Security (for SQL 2000) is far superior to SQL
authentication, and far better than trying to keep a USERS table up to date.
In SQL Server, use the SYSTEM_USER system function to retrieve the users
login name (in the form of [domain\username].)
Example:
SELECT SYSTEM_USER
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uJVfx3ntGHA.2392@.TK2MSFTNGP05.phx.gbl...
>I am working on making my application inherit the workstation login user
>information so that users aren't presented with multiple logins.
> Is it safe enough to use Integrated Security via the connection string. If
> that succeeds, read the windows user name and check that against an
> internal username?
> Does anyone have any experience linking internal application user
> management (ie: USERS table) with windows authentication?
> Any comments or suggestions would be great.
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Mmz2potGHA.324@.TK2MSFTNGP06.phx.gbl...
> First key point: By using Windows authentication, you do NOT have to have
> a USERS table. In SQL Server, you assign all permissions to the Windows
> login or the Windows network group. (It makes life so much easier for the
> DBA.)
> Windows Integrated Security (for SQL 2000) is far superior to SQL
> authentication, and far better than trying to keep a USERS table up to
> date.
> In SQL Server, use the SYSTEM_USER system function to retrieve the users
> login name (in the form of [domain\username].)
> Example:
> SELECT SYSTEM_USER
> --
Thanks Arnie. That call is very helpful.
However, I have access rights that a specific to my application such as menu
options, reports, specific actions, etc. The application was developed 8
years ago and supported multiple database platforms. We have moved to only
supporting MS SQL Server 7, 2000 and 2005.
What's the best way to link these together since DBAs wouldn't be able to
assign my application specific access rights via MS SQL Server Management
Studio/Enterprise Manager?