Database design is a lot harder than I thought. I'm going through a few
books, but I can't figure this one out...
Imagine a database to track employee timecards. We'd have information on
employees, job descriptions, hours worked, and things like that. Pretty
basic stuff.
Now imagine, for example, a dotcom which wants to store timecard infomation
for thousands of companies. The table with the employee ID, company name,
and hours worked would get huge since there'd be just one table with
information about all employees, regardless of their company. This seems
like a problem.
Is the solution to partition the table so that rows pertaining to each
company are stored on the same server? Or, horizontally partition the huge
table so that a year's worth of timecard info is split into 12 partitions,
one for each month? Or ...?
Or, how about making a new database for each company which subscribes to the
service? SS2000 can support 32,000+ databases, but the overhead for running
thousands of databases simultaneously must be huge.
any thoughts?
Josh> Now imagine, for example, a dotcom which wants to store timecard
infomation
> for thousands of companies. The table with the employee ID, company name,
> and hours worked would get huge since there'd be just one table with
> information about all employees, regardless of their company. This seems
> like a problem.
Not really. Any table that is properly designed and indexed will work fine
with tens of millions of rows.
You might however want to create a separate database for each customer for
security reasons (timecard information is a sensitive issue, just ask
British Airways), and that might solve some potential performance problems.
I don't think the overhead for running multiple databases on one server is
very noticable.
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message
news:#LAiZfNVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Database design is a lot harder than I thought. I'm going through a few
> books, but I can't figure this one out...
> Imagine a database to track employee timecards. We'd have information on
> employees, job descriptions, hours worked, and things like that. Pretty
> basic stuff.
> Now imagine, for example, a dotcom which wants to store timecard
infomation
> for thousands of companies. The table with the employee ID, company name,
> and hours worked would get huge since there'd be just one table with
> information about all employees, regardless of their company. This seems
> like a problem.
> Is the solution to partition the table so that rows pertaining to each
> company are stored on the same server? Or, horizontally partition the
huge
> table so that a year's worth of timecard info is split into 12 partitions,
> one for each month? Or ...?
> Or, how about making a new database for each company which subscribes to
the
> service? SS2000 can support 32,000+ databases, but the overhead for
running
> thousands of databases simultaneously must be huge.
> any thoughts?
> Josh
>|||I would imagine the the "huge" table would consist in the main of integer
datatypes (FKs back top the main tables). This should keep the table Size,
physical down.
Indexing is Key. Choose your clustered index carefully. Michelle Poolet
had a good article in SQL Mag a while back about it
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=27334
Before Andrew gives you a huge KB list of resources <grin> here is something
you will also need to consider
Disk Subsystem. Make it a fast RAID array (0+1)
Put logs on a another array
TempDB on Another
Database design plays a large part in application efficiency. It is good
you are reading up on things before just throwing something at a database
server.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message
news:#LAiZfNVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Database design is a lot harder than I thought. I'm going through a few
> books, but I can't figure this one out...
> Imagine a database to track employee timecards. We'd have information on
> employees, job descriptions, hours worked, and things like that. Pretty
> basic stuff.
> Now imagine, for example, a dotcom which wants to store timecard
infomation
> for thousands of companies. The table with the employee ID, company name,
> and hours worked would get huge since there'd be just one table with
> information about all employees, regardless of their company. This seems
> like a problem.
> Is the solution to partition the table so that rows pertaining to each
> company are stored on the same server? Or, horizontally partition the
huge
> table so that a year's worth of timecard info is split into 12 partitions,
> one for each month? Or ...?
> Or, how about making a new database for each company which subscribes to
the
> service? SS2000 can support 32,000+ databases, but the overhead for
running
> thousands of databases simultaneously must be huge.
> any thoughts?
> Josh
>|||Jacco's post deserves to be co-signed. Definitely
consider the security of separate databases. The
additional overhead should be neglegible, but the security
benefits are potentially huge.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>> Now imagine, for example, a dotcom which wants to store
timecard
>infomation
>> for thousands of companies. The table with the
employee ID, company name,
>> and hours worked would get huge since there'd be just
one table with
>> information about all employees, regardless of their
company. This seems
>> like a problem.
>Not really. Any table that is properly designed and
indexed will work fine
>with tens of millions of rows.
>You might however want to create a separate database for
each customer for
>security reasons (timecard information is a sensitive
issue, just ask
>British Airways), and that might solve some potential
performance problems.
>I don't think the overhead for running multiple databases
on one server is
>very noticable.
>
>--
>Jacco Schalkwijk MCDBA, MCSD, MCSE
>Database Administrator
>Eurostop Ltd.
>
>"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message
>news:#LAiZfNVDHA.2368@.TK2MSFTNGP09.phx.gbl...
>> Database design is a lot harder than I thought. I'm
going through a few
>> books, but I can't figure this one out...
>> Imagine a database to track employee timecards. We'd
have information on
>> employees, job descriptions, hours worked, and things
like that. Pretty
>> basic stuff.
>> Now imagine, for example, a dotcom which wants to store
timecard
>infomation
>> for thousands of companies. The table with the
employee ID, company name,
>> and hours worked would get huge since there'd be just
one table with
>> information about all employees, regardless of their
company. This seems
>> like a problem.
>> Is the solution to partition the table so that rows
pertaining to each
>> company are stored on the same server? Or,
horizontally partition the
>huge
>> table so that a year's worth of timecard info is split
into 12 partitions,
>> one for each month? Or ...?
>> Or, how about making a new database for each company
which subscribes to
>the
>> service? SS2000 can support 32,000+ databases, but the
overhead for
>running
>> thousands of databases simultaneously must be huge.
>> any thoughts?
>> Josh
>>
>
>.
>|||> Now imagine, for example, a dotcom which wants to store timecard
infomation
> for thousands of companies. The table with the employee ID, company
name,
> and hours worked would get huge since there'd be just one table with
> information about all employees, regardless of their company. This
seems
> like a problem.
1000000 employees x 250 days = 250000000 rows/year. This is not large
by today's standards. Assuming you store about 100 bytes per row,
you'll only have 25GB. Horizontal partitioning is usually employed to
facilitate scale-out and administration, not performance on a single
server. I suggest that you avoid addressing performance problems in
your database design unless you are certain you will have a problem.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Josh Meyer" <jmeyer@.msg.ucsf.edu> wrote in message
news:%23LAiZfNVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Database design is a lot harder than I thought. I'm going through a
few
> books, but I can't figure this one out...
> Imagine a database to track employee timecards. We'd have information
on
> employees, job descriptions, hours worked, and things like that.
Pretty
> basic stuff.
> Now imagine, for example, a dotcom which wants to store timecard
infomation
> for thousands of companies. The table with the employee ID, company
name,
> and hours worked would get huge since there'd be just one table with
> information about all employees, regardless of their company. This
seems
> like a problem.
> Is the solution to partition the table so that rows pertaining to each
> company are stored on the same server? Or, horizontally partition the
huge
> table so that a year's worth of timecard info is split into 12
partitions,
> one for each month? Or ...?
> Or, how about making a new database for each company which subscribes
to the
> service? SS2000 can support 32,000+ databases, but the overhead for
running
> thousands of databases simultaneously must be huge.
> any thoughts?
> Josh
>|||"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23vS9U2QVDHA.2340@.TK2MSFTNGP10.phx.gbl...
> 1000000 employees x 250 days = 250000000 rows/year. This is not large
> by today's standards. Assuming you store about 100 bytes per row,
> you'll only have 25GB. Horizontal partitioning is usually employed to
> facilitate scale-out and administration, not performance on a single
> server.
Yup, I was thinking I'd have to scale-out in the future thinking that
thousands of HR departments would hit the server simultaneously to look up
timecard information for their employees. As the load on all federated
servers, I could just add another machine and partition things a little
more.
250 million rows a year might not be very large, but how about 10,000
clients hitting the database at the same time. am i correct in assuming i'd
need several servers to support that?
Josh
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
Database design help pls-Landuse
Trying to help a friend design a database for a new project. Trying to figure out entities i need. This will be use on web project, interface will be link to geographical map showing all State in US.
please advise
Thanks
Here's the business infor he gave me
Landuse is a public service network of county and city municipal databases designed to present geographically indexed FACTS for online Internet distribution during the busy and competitive decision-making process.
FACTS are an acronym for: Fast, Accurate, Comprehensive, Timely, Systematic.
FACTS are abstracted; geo-indexed and made available for Internet distribution by Landuse. This daily updated public information service is an important analytical research tool for responsive government agencies and the general public. It is worthy of your inspection and trust
Landuse is a simple and unique map-driven application used to bookmark nationwide real estate databases with menu-driven Township Locator Maps that will visually identify regions, tracts and/or parcels of property relative to pubic information abstracts of record.
Once you have selected a state indexed with Township Locator Maps, Landuse is a four-step drilldown procedure:
Step One is the Township Locator Map, a copyrighted master plan for identifying each and all of the townships in a particular state with four characters [0000].
Step Two is selecting one or more sections; each surveyed to contain 640 acres (more or less) and is numbered, one through thirty-six, within the selected township [01 to 36].
Step Three will produce a selected section divided into four-quarter sections lettered with the NE [1], NW [2], SW [3], SE [4] containing 160 acres, (more or less).
Local county tax assessors prepare property tax maps annually. Each parcel is identified by an assigned tax map parcel identifier, which is our key to index and attach public recorded documents maintained in county courthouses and news releases published by local county newspaper press organizations.
Step Four is a Landuse dual-purpose menu for access to Docufiles (courthouse) and Profiles (newspapers) referenced to the subject property. Daily public information is indexed to the parcel identifiers for access by member associations, government agencies, and corporate sponsors. Upon receipt of daily public information, the data is made available for Internet service, 36 hours later, on a daily, 24-hour, seven-day basisYou really need to talk to your friend about what they've got in mind... They are WAY off on some of their basic assumptions already. If you designed a database to represent land use the way that text describes it, the mis-match between what you store and the real world will be so bad that there will probably be no real use for what you've created.
The MAPS project done for NOAA should be public information by now. They tried to do almost exactly what this seems to be doing, for the purposes of coordinating weather data and alerting the responsible authorities (to remove the dependancy on Weather Reporting Stations for the dissemination of alerts). That project is still underway as far as I know, with ten years of development without a consensus on how to map satelite observations and WRS (Weather Reporting Stations) to responsible authorities!
Until you get a model that more closely resembles the real world, pretty much any effort you put into the project will be wasted.
-PatP
please advise
Thanks
Here's the business infor he gave me
Landuse is a public service network of county and city municipal databases designed to present geographically indexed FACTS for online Internet distribution during the busy and competitive decision-making process.
FACTS are an acronym for: Fast, Accurate, Comprehensive, Timely, Systematic.
FACTS are abstracted; geo-indexed and made available for Internet distribution by Landuse. This daily updated public information service is an important analytical research tool for responsive government agencies and the general public. It is worthy of your inspection and trust
Landuse is a simple and unique map-driven application used to bookmark nationwide real estate databases with menu-driven Township Locator Maps that will visually identify regions, tracts and/or parcels of property relative to pubic information abstracts of record.
Once you have selected a state indexed with Township Locator Maps, Landuse is a four-step drilldown procedure:
Step One is the Township Locator Map, a copyrighted master plan for identifying each and all of the townships in a particular state with four characters [0000].
Step Two is selecting one or more sections; each surveyed to contain 640 acres (more or less) and is numbered, one through thirty-six, within the selected township [01 to 36].
Step Three will produce a selected section divided into four-quarter sections lettered with the NE [1], NW [2], SW [3], SE [4] containing 160 acres, (more or less).
Local county tax assessors prepare property tax maps annually. Each parcel is identified by an assigned tax map parcel identifier, which is our key to index and attach public recorded documents maintained in county courthouses and news releases published by local county newspaper press organizations.
Step Four is a Landuse dual-purpose menu for access to Docufiles (courthouse) and Profiles (newspapers) referenced to the subject property. Daily public information is indexed to the parcel identifiers for access by member associations, government agencies, and corporate sponsors. Upon receipt of daily public information, the data is made available for Internet service, 36 hours later, on a daily, 24-hour, seven-day basisYou really need to talk to your friend about what they've got in mind... They are WAY off on some of their basic assumptions already. If you designed a database to represent land use the way that text describes it, the mis-match between what you store and the real world will be so bad that there will probably be no real use for what you've created.
The MAPS project done for NOAA should be public information by now. They tried to do almost exactly what this seems to be doing, for the purposes of coordinating weather data and alerting the responsible authorities (to remove the dependancy on Weather Reporting Stations for the dissemination of alerts). That project is still underway as far as I know, with ten years of development without a consensus on how to map satelite observations and WRS (Weather Reporting Stations) to responsible authorities!
Until you get a model that more closely resembles the real world, pretty much any effort you put into the project will be wasted.
-PatP
Friday, February 17, 2012
DataBase Creation
I am developing and application which requires a database and 7 tables.
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||I have seen companies do both... The problem I have with delivering mdf and
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||One of our 10 commandments here is "Always script".
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's
>--Original Message--
>If you want to supply your database with some pre-
defined values are some
>data records, sending a backup of the database might be
a good option, since
>all the users have to do is to restore the database at
their end using
>Enterprise Manager. If you want to avoid this hazzle,
you can create the
>scripts for the database along with table creation and
data insertion
>scripts and then have it execute. The "pubs" database of
SQL Server is done
>in this way. If you have SQL Server installed, you can
see how this script
>is structured, by navigating to: C:\Program
Files\Microsoft SQL
>Server\MSSQL\Install\instpubs.sql
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
>news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
>> I am developing and application which requires a
database and 7 tables.
>> I am trying to figure out how to do the installation
of this database and
>> what approach to installing a database is appropriate,
as I have never
>> installed an application which requires the
installation of database.
>> I was hoping someone could point me in the direction
of the "appropriate
>> way to do this".
>> Should I create the script files for the database and
tables ind then
>> execute that SQL to build the tables, possibly
prompting the user for the
>> location as to where the tables should be installed?
>> Should I do a Database backup of an "empty" database
structure (database
>> and tables) and then do some sort of restore of the
database through my
>> application?
>> What is the appropriate technique to this type of
installation?
>>
>> Thanks in advance for your assistance!!!!!!
>
>.
>
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||I have seen companies do both... The problem I have with delivering mdf and
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!|||One of our 10 commandments here is "Always script".
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's
>--Original Message--
>If you want to supply your database with some pre-
defined values are some
>data records, sending a backup of the database might be
a good option, since
>all the users have to do is to restore the database at
their end using
>Enterprise Manager. If you want to avoid this hazzle,
you can create the
>scripts for the database along with table creation and
data insertion
>scripts and then have it execute. The "pubs" database of
SQL Server is done
>in this way. If you have SQL Server installed, you can
see how this script
>is structured, by navigating to: C:\Program
Files\Microsoft SQL
>Server\MSSQL\Install\instpubs.sql
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
>news:Xns946EDAE2F587DJimHeaveyhotmailcom@.207.46.248.16...
>> I am developing and application which requires a
database and 7 tables.
>> I am trying to figure out how to do the installation
of this database and
>> what approach to installing a database is appropriate,
as I have never
>> installed an application which requires the
installation of database.
>> I was hoping someone could point me in the direction
of the "appropriate
>> way to do this".
>> Should I create the script files for the database and
tables ind then
>> execute that SQL to build the tables, possibly
prompting the user for the
>> location as to where the tables should be installed?
>> Should I do a Database backup of an "empty" database
structure (database
>> and tables) and then do some sort of restore of the
database through my
>> application?
>> What is the appropriate technique to this type of
installation?
>>
>> Thanks in advance for your assistance!!!!!!
>
>.
>
DataBase Creation
I am developing and application which requires a database and 7 tables.
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's
defined values are some
a good option, since
their end using
you can create the
data insertion
SQL Server is done
see how this script
Files\Microsoft SQL
I am trying to figure out how to do the installation of this database and
what approach to installing a database is appropriate, as I have never
installed an application which requires the installation of database.
I was hoping someone could point me in the direction of the "appropriate
way to do this".
Should I create the script files for the database and tables ind then
execute that SQL to build the tables, possibly prompting the user for the
location as to where the tables should be installed?
Should I do a Database backup of an "empty" database structure (database
and tables) and then do some sort of restore of the database through my
application?
What is the appropriate technique to this type of installation?
Thanks in advance for your assistance!!!!!!If you want to supply your database with some pre-defined values are some
data records, sending a backup of the database might be a good option, since
all the users have to do is to restore the database at their end using
Enterprise Manager. If you want to avoid this hazzle, you can create the
scripts for the database along with table creation and data insertion
scripts and then have it execute. The "pubs" database of SQL Server is done
in this way. If you have SQL Server installed, you can see how this script
is structured, by navigating to: C:\Program Files\Microsoft SQL
Server\MSSQL\Install\instpubs.sql
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
quote:|||I have seen companies do both... The problem I have with delivering mdf and
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!
ldf files or backup files, is that
1. These files may have a limited life span, since MS could change the
file format
2. You would have pre-selected collation and initial file sizes and
layout
I prefer using scripts, then you are covered... Then high end user can then
create the database in any way they see fit and run your scripts to setup
the tables and data...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
quote:|||One of our 10 commandments here is "Always script".
> I am developing and application which requires a database and 7 tables.
> I am trying to figure out how to do the installation of this database and
> what approach to installing a database is appropriate, as I have never
> installed an application which requires the installation of database.
> I was hoping someone could point me in the direction of the "appropriate
> way to do this".
> Should I create the script files for the database and tables ind then
> execute that SQL to build the tables, possibly prompting the user for the
> location as to where the tables should be installed?
> Should I do a Database backup of an "empty" database structure (database
> and tables) and then do some sort of restore of the database through my
> application?
> What is the appropriate technique to this type of installation?
>
> Thanks in advance for your assistance!!!!!!
We script table creation - index creation - table format
changes - everything. We script BULK INSERT's from .txt
files to pre-fill tables with data. We script the
dropping and re-creation of stored procedures.
We script the "GRANT" of access/rights/etc.
If someone makes a change in Enterprise Manager we would
never know to get it into production. That would be a
disaster.
Been working very well for us. We are simultaneously
developing three large enterprise apps - migrating from
mainframes (VAX's) that have been in use since 1980's
quote:
>--Original Message--
>If you want to supply your database with some pre-
defined values are some
quote:
>data records, sending a backup of the database might be
a good option, since
quote:
>all the users have to do is to restore the database at
their end using
quote:
>Enterprise Manager. If you want to avoid this hazzle,
you can create the
quote:
>scripts for the database along with table creation and
data insertion
quote:
>scripts and then have it execute. The "pubs" database of
SQL Server is done
quote:
>in this way. If you have SQL Server installed, you can
see how this script
quote:
>is structured, by navigating to: C:\Program
Files\Microsoft SQL
quote:
>Server\MSSQL\Install\instpubs.sql
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
> news:Xns946EDAE2F587DJimHeaveyhotmailcom
@.207.46.248.16...
database and 7 tables.[QUOTE]
of this database and[QUOTE]
as I have never[QUOTE]
installation of database.[QUOTE]
of the "appropriate[QUOTE]
tables ind then[QUOTE]
prompting the user for the[QUOTE]
structure (database[QUOTE]
database through my[QUOTE]
installation?[QUOTE]
>
>.
>
Subscribe to:
Posts (Atom)