Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data?
Any Help Will Be Appreciated
Hi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>
|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David
|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data?
> Any Help Will Be Appreciated
>

Database Structure

Hi,
Is there a way to automate a process that export the database strucuture once a day !
All the objects - Tables, Indexes, Procedures, Views Etc..
Any Help I apreciate !
Thank's
You could run a sql agent job that uses SQL-DMO to script your database.
Here is an article that I wrote that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:5964A3E3-E149-4E4D-812D-B6B62FE9C878@.microsoft.com...
> Hi,
> Is there a way to automate a process that export the database strucuture
once a day !
> All the objects - Tables, Indexes, Procedures, Views Etc..
> Any Help I apreciate !
> Thank's
>
sql

Database Structure

Hi Everyone
I Have a Question Concerning DataBase Structure,
If i have a database that contains All Master Tables [user acount,user
detail &...] & i have to make another module for the same system that will
use the same master tables
Is it Preferred To Construct A New Database for this module & any any other
new module or make it all in the same database because they all shared the
same master Data'
Any Help Will Be AppreciatedHi
Size tends to be one of the drivers as to whether you should partition, if
it is a reasonable size then keep them together. If you used views to access
the data then it would be quite easy to partition it at a later point.
John
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||As John Suggests, Absolutely, positively use views so you can move things if
you wish..
I prefer ( if size permits) to have everything in a single database...
However you may wish to place different modules in different filegroups IF
you think you may wish to backup/restore a module independently of the
others..
If you put things in different databases, remember things can get out of
sync, unless you shut everything down for backups... Also there can be no
cross-database referential integrity...
Try to put them together in the db, but separate if you must.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>|||"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eXcEc7keFHA.2736@.TK2MSFTNGP12.phx.gbl...
> As John Suggests, Absolutely, positively use views so you can move things
> if you wish..
> I prefer ( if size permits) to have everything in a single database...
> However you may wish to place different modules in different filegroups IF
> you think you may wish to backup/restore a module independently of the
> others..
> If you put things in different databases, remember things can get out of
> sync, unless you shut everything down for backups... Also there can be no
> cross-database referential integrity...
> Try to put them together in the db, but separate if you must.
>
I agree. But I would go further and say that when you are designing a
system from the ground-up, you never "must". If you think you must seperate
related objects into different databases, think again. Schemas, FileGroups,
views, permissions, etc will usually let you keep the objects in one
database.
David|||If you place your master data in several databases, then you may end up with
lots of duplicate for indexes, views, triggers, procedures etc, it probably
does not worth unless your table will be really big.
"Mariame" <mariame_waguih@.hotmail.com> wrote in message
news:uPlhibieFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Hi Everyone
> I Have a Question Concerning DataBase Structure,
> If i have a database that contains All Master Tables [user acount,user
> detail &...] & i have to make another module for the same system that
> will use the same master tables
> Is it Preferred To Construct A New Database for this module & any any
> other new module or make it all in the same database because they all
> shared the same master Data'
> Any Help Will Be Appreciated
>

Database schema differences

Hello,
Is there any way to compare two schemas and see the differences. Usual
story - client has changed their database structure, sent me a new
copy and I need to know what's changed without looking at each
table...best I've come up with so far is to script each database and
look at the scripts in Notepad...me thinks there must be a better way.
Cheers
Ray
Hi
Tools such as Red Gates SQL Compare, DBGhost etc can do that and also script
the changes needed to return it back to what it should be!
John
"rbrowning1958" <RBrowning1958@.gmail.com> wrote in message
news:91e2428a-0140-468c-8420-d3b3a09cb18f@.s37g2000prg.googlegroups.com...
> Hello,
> Is there any way to compare two schemas and see the differences. Usual
> story - client has changed their database structure, sent me a new
> copy and I need to know what's changed without looking at each
> table...best I've come up with so far is to script each database and
> look at the scripts in Notepad...me thinks there must be a better way.
> Cheers
> Ray
|||The free, open-source SchemaCrawler for SQL Server tool will do this
for you. You can take human-readable snapshots of the schema and data,
for later comparison. Comparisons are done using a standard diff tool
such as WinMerge. SchemaCrawler outputs details of your schema
(tables, views, procedures, and more) in a diff-able plain-text format
(text, CSV, or XHTML). SchemaCrawler can also output data (including
CLOBs and BLOBs) in the same plain-text formats.
SchemaCrawler is available at SourceForge:
http://schemacrawler.sourceforge.net/
Sualeh Fatehi
|||Try AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com
- you can easily compare structures of tables, procedures, functions,
views, triggers and relationships.
Comparison "sessions" can be saved and re-played later without the
need of re-entering the parameters;
command line parameter can be used to fully automate comparisons.
Dariusz Dziewialtowski.

Database schema differences

Hello,
Is there any way to compare two schemas and see the differences. Usual
story - client has changed their database structure, sent me a new
copy and I need to know what's changed without looking at each
table...best I've come up with so far is to script each database and
look at the scripts in Notepad...me thinks there must be a better way.
Cheers
RayHi
Tools such as Red Gates SQL Compare, DBGhost etc can do that and also script
the changes needed to return it back to what it should be!
John
"rbrowning1958" <RBrowning1958@.gmail.com> wrote in message
news:91e2428a-0140-468c-8420-d3b3a09cb18f@.s37g2000prg.googlegroups.com...
> Hello,
> Is there any way to compare two schemas and see the differences. Usual
> story - client has changed their database structure, sent me a new
> copy and I need to know what's changed without looking at each
> table...best I've come up with so far is to script each database and
> look at the scripts in Notepad...me thinks there must be a better way.
> Cheers
> Ray|||The free, open-source SchemaCrawler for SQL Server tool will do this
for you. You can take human-readable snapshots of the schema and data,
for later comparison. Comparisons are done using a standard diff tool
such as WinMerge. SchemaCrawler outputs details of your schema
(tables, views, procedures, and more) in a diff-able plain-text format
(text, CSV, or XHTML). SchemaCrawler can also output data (including
CLOBs and BLOBs) in the same plain-text formats.
SchemaCrawler is available at SourceForge:
http://schemacrawler.sourceforge.net/
Sualeh Fatehi|||Try AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com
- you can easily compare structures of tables, procedures, functions,
views, triggers and relationships.
Comparison "sessions" can be saved and re-played later without the
need of re-entering the parameters;
command line parameter can be used to fully automate comparisons.
Dariusz Dziewialtowski.

Database refresh question

There's a sql server 2000 database that was created as a copy of another db, let's say db1 and copy_of_db1. db1 has been updated (structure and data) since copy_of_db1 was created, while copy_of_db1 has remained static. I now need to update copy_of_db1 to be in sync with db1 and use copy_of_db1 so I can drop db1. What would be the fastest and most efficient way to update copy_of_db1 to mirror the current db1?

Backup the updated one and restore it, in the Backup and restore wizard choose the restore from device option you also have the option to change the name of the restored one. Hope this helps.

|||Thanks!

Database optymalization(?)

Hallo
I need a help in optymalization database. My database is much slower
than bigger databeses(the same structure, only data is different). The
porblem is that i can't interfer in the structure - can't change
procedures, view, select ect..

I dont have any ideas how to speed it up or where can be the problem.

I read some articles I but i still need more information.

Please send me some advices or links.

Greatings

RoanROAN (roan@.autograf.pl) writes:
> I need a help in optymalization database. My database is much slower
> than bigger databeses(the same structure, only data is different). The
> porblem is that i can't interfer in the structure - can't change
> procedures, view, select ect..
> I dont have any ideas how to speed it up or where can be the problem.
> I read some articles I but i still need more information.

The first step is to gather information. Exactly which queries are
running slowly? Which query plans do they have? Could they benefit
from an index? If a certain query runs fine in the other database,
are there any differences in indexing?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog napisal(a):
> ROAN (roan@.autograf.pl) writes:
> > I need a help in optymalization database. My database is much slower
> > than bigger databeses(the same structure, only data is different). The
> > porblem is that i can't interfer in the structure - can't change
> > procedures, view, select ect..
> > I dont have any ideas how to speed it up or where can be the problem.
> > I read some articles I but i still need more information.
> The first step is to gather information. Exactly which queries are
> running slowly? Which query plans do they have? Could they benefit
> from an index? If a certain query runs fine in the other database,
> are there any differences in indexing?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

And there is a problem... all indexes are the same(definition) in every
datatabase, the difference is only in data. So i dont think there is a
problem.
I will be looking slow queries...
But i still waiting for some aditional advices...
Greatings|||ROAN (roan@.autograf.pl) writes:
> And there is a problem... all indexes are the same(definition) in every
> datatabase, the difference is only in data. So i dont think there is a
> problem.
> I will be looking slow queries...
> But i still waiting for some aditional advices...

Statistics are not likely to be the same, as the databases are of different
sizes. And they can be out of date (even if SQL Server maintains statistics
automatically).

You can also be victim to fragmentation. A DBCC SHOWCONTIG on key tables
can reveals this. Reindexing will also fix statistics.

Also keep in mind that the optimizer determines the query plan from
*estimates* and estimates can be wrong for one reason or another. Sometimes
even if statistics are correct.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp