Database Design - newbie question

Hi ,
First of all my apologies for this very basic question but I can n't find a
definite answers to this question.
I'm new to data warehousing and have started reading "The Data Warehouse
Lifecycle Toolkit" by Kimbal (and others). From it I understood that the
database in your application (normalised) is very differenent to the one in
your warehouse (denormalised). But from reading about SQL Server analysis
server it does n't seem to matter what you use as your source as you can
create your cubes from an application database design.
Is this right? are there any drawbacks to this. It would mean that we won't
need another table design for our warehouse database.
Thanks,
Nuala
What differentiates the data warehouse design from the online transactional
database is the following:
1. To optimize the OLTP database, historical records are often migrated
to the warehouse, and it is from here that trend reports / analysis are
generated. Also, the indexing on the warehouse is optimized for analysis
purposes. The warehouse typically has much more indexing.
2. The warehouse databases are typically located on a different server
that is used exclusively for reporting and analysis. This reduces the load
on the OLTP server.
3. To improve the performace of analysis, data in the warehouse may be
stored in multiple levels of summarization, and most analysis may only be
derived from the smaller summarized datasets.
So the idea of data warehousing is that you perform your analysis against
summarized and highly indexed data instead of at the transactional level.
"Nuala" <nuala.cullen@.prnewswire.co.uk> wrote in message
news:BE655942-E657-46BF-AEAD-9A607AEEAB6F@.microsoft.com...
> Hi ,
> First of all my apologies for this very basic question but I can n't find
a
> definite answers to this question.
> I'm new to data warehousing and have started reading "The Data Warehouse
> Lifecycle Toolkit" by Kimbal (and others). From it I understood that the
> database in your application (normalised) is very differenent to the one
in
> your warehouse (denormalised). But from reading about SQL Server analysis
> server it does n't seem to matter what you use as your source as you can
> create your cubes from an application database design.
> Is this right? are there any drawbacks to this. It would mean that we
won't
> need another table design for our warehouse database.
> Thanks,
> --
> Nuala
|||Quoting JT in microsoft.public.sqlserver.datawarehouse:

>So the idea of data warehousing is that you perform your analysis against
>summarized and highly indexed data instead of at the transactional level.
Recommended reading (since I've just read it... )
"The impact of alternative diagrams on the accuracy of recall:
A comparison of star-schema diagrams and entity-relationship diagrams"
Corral et al., ' www.sciencedirect.com ' .
All that we see, or seem,
is but a dream, within a dream,
installed by the Machine

Database Design - newbie question

Hi ,
First of all my apologies for this very basic question but I can n't find a
definite answers to this question.
I'm new to data warehousing and have started reading "The Data Warehouse
Lifecycle Toolkit" by Kimbal (and others). From it I understood that the
database in your application (normalised) is very differenent to the one in
your warehouse (denormalised). But from reading about SQL Server analysis
server it does n't seem to matter what you use as your source as you can
create your cubes from an application database design.
Is this right? are there any drawbacks to this. It would mean that we won't
need another table design for our warehouse database.
Thanks,
NualaWhat differentiates the data warehouse design from the online transactional
database is the following:
1. To optimize the OLTP database, historical records are often migrated
to the warehouse, and it is from here that trend reports / analysis are
generated. Also, the indexing on the warehouse is optimized for analysis
purposes. The warehouse typically has much more indexing.
2. The warehouse databases are typically located on a different server
that is used exclusively for reporting and analysis. This reduces the load
on the OLTP server.
3. To improve the performace of analysis, data in the warehouse may be
stored in multiple levels of summarization, and most analysis may only be
derived from the smaller summarized datasets.
So the idea of data warehousing is that you perform your analysis against
summarized and highly indexed data instead of at the transactional level.
"Nuala" <nuala.cullen@.prnewswire.co.uk> wrote in message
news:BE655942-E657-46BF-AEAD-9A607AEEAB6F@.microsoft.com...
> Hi ,
> First of all my apologies for this very basic question but I can n't find
a
> definite answers to this question.
> I'm new to data warehousing and have started reading "The Data Warehouse
> Lifecycle Toolkit" by Kimbal (and others). From it I understood that the
> database in your application (normalised) is very differenent to the one
in
> your warehouse (denormalised). But from reading about SQL Server analysis
> server it does n't seem to matter what you use as your source as you can
> create your cubes from an application database design.
> Is this right? are there any drawbacks to this. It would mean that we
won't
> need another table design for our warehouse database.
> Thanks,
> --
> Nuala|||Quoting JT in microsoft.public.sqlserver.datawarehouse:

>So the idea of data warehousing is that you perform your analysis against
>summarized and highly indexed data instead of at the transactional level.
Recommended reading (since I've just read it... )
"The impact of alternative diagrams on the accuracy of recall:
A comparison of star-schema diagrams and entity-relationship diagrams"
Corral et al., ' www.sciencedirect.com ' .
All that we see, or seem,
is but a dream, within a dream,
installed by the Machine

Database deployment

Hi there
Firstly, apologies for the multi-post - I could not decide which group would
be best to post this to.
I have a simple (I think) question.
We are about to roll out our first SQL server based application. The
application and run times are no problems - I'm pretty sure I know what they
all are.
But, how do I move our database from our server, onto our customers
server?
Thanks
Gareth Sharp
you have a couple of option, two that pop up into my head (this early) are
backup and restore
detach and attach
http://sqlservercode.blogspot.com/
"Gareth Sharp" wrote:

> Hi there
> Firstly, apologies for the multi-post - I could not decide which group would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
>
|||Hi,
If the database size is small just backup and write in CD or TAPE.
and Restore it on the client side.
Also backup the master,msdb and model database.
from
Doller
|||doller wrote:
> Hi,
> If the database size is small just backup and write in CD or TAPE.
> and Restore it on the client side.
> Also backup the master,msdb and model database.
> from
> Doller
>
Why would you backup Master, MSDB and Model in this case? I'd just
backup the user database and restore it on the customers server.
Another option could be to script out the database etc. and then run
this script on the customers server.
Regards
Steen
|||We use SQL scripts rather than backup/restore or detach/attach. This
provides both new and upgrade install functionality and works well since we
also keep or DDL scripts under source control.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||These may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||I also prefer scripts instead of a binary copy of the database. Among other things, when a binary
copy is distributed, you need to make 100% certain that the app work with different collation on the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> We use SQL scripts rather than backup/restore or detach/attach. This provides both new and
> upgrade install functionality and works well since we also keep or DDL scripts under source
> control.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
>
|||hi Gareth,
Gareth Sharp wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which
> group would be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know
> what they all are.
> But, how do I move our database from our server, onto our customers
> server?
I'm with Dan and Tibor...
try having a look at
http://msdn.microsoft.com/msdnmag/is...baseinstaller/ ...
it's a good article and implies support for versioning and source control..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||And you need to remap users to logins on the new server as well when you use
a backup, because the mapping uses the SID. Scripts will just generate the
user and map it to the login without using a autogenerated ID.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMy3dGAzFHA.3856@.tk2msftngp13.phx.gbl...
>I also prefer scripts instead of a binary copy of the database. Among other
>things, when a binary copy is distributed, you need to make 100% certain
>that the app work with different collation on the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>

Database deployment

Hi there
Firstly, apologies for the multi-post - I could not decide which group would
be best to post this to.
I have a simple (I think) question.
We are about to roll out our first SQL server based application. The
application and run times are no problems - I'm pretty sure I know what they
all are.
But, how do I move our database from our server, onto our customers
server?
Thanks
Gareth Sharp
you have a couple of option, two that pop up into my head (this early) are
backup and restore
detach and attach
http://sqlservercode.blogspot.com/
"Gareth Sharp" wrote:

> Hi there
> Firstly, apologies for the multi-post - I could not decide which group would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
>
|||Hi,
If the database size is small just backup and write in CD or TAPE.
and Restore it on the client side.
Also backup the master,msdb and model database.
from
Doller
|||doller wrote:
> Hi,
> If the database size is small just backup and write in CD or TAPE.
> and Restore it on the client side.
> Also backup the master,msdb and model database.
> from
> Doller
>
Why would you backup Master, MSDB and Model in this case? I'd just
backup the user database and restore it on the customers server.
Another option could be to script out the database etc. and then run
this script on the customers server.
Regards
Steen
|||We use SQL scripts rather than backup/restore or detach/attach. This
provides both new and upgrade install functionality and works well since we
also keep or DDL scripts under source control.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||These may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||I also prefer scripts instead of a binary copy of the database. Among other things, when a binary
copy is distributed, you need to make 100% certain that the app work with different collation on the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> We use SQL scripts rather than backup/restore or detach/attach. This provides both new and
> upgrade install functionality and works well since we also keep or DDL scripts under source
> control.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
>
|||hi Gareth,
Gareth Sharp wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which
> group would be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know
> what they all are.
> But, how do I move our database from our server, onto our customers
> server?
I'm with Dan and Tibor...
try having a look at
http://msdn.microsoft.com/msdnmag/is...baseinstaller/ ...
it's a good article and implies support for versioning and source control..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||And you need to remap users to logins on the new server as well when you use
a backup, because the mapping uses the SID. Scripts will just generate the
user and map it to the login without using a autogenerated ID.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMy3dGAzFHA.3856@.tk2msftngp13.phx.gbl...
>I also prefer scripts instead of a binary copy of the database. Among other
>things, when a binary copy is distributed, you need to make 100% certain
>that the app work with different collation on the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>

Database deployment

Hi there
Firstly, apologies for the multi-post - I could not decide which group would
be best to post this to.
I have a simple (I think) question.
We are about to roll out our first SQL server based application. The
application and run times are no problems - I'm pretty sure I know what they
all are.
But, how do I move our database from our server, onto our customers
server?
Thanks
Gareth Sharp
you have a couple of option, two that pop up into my head (this early) are
backup and restore
detach and attach
http://sqlservercode.blogspot.com/
"Gareth Sharp" wrote:

> Hi there
> Firstly, apologies for the multi-post - I could not decide which group would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
>
|||Hi,
If the database size is small just backup and write in CD or TAPE.
and Restore it on the client side.
Also backup the master,msdb and model database.
from
Doller
|||doller wrote:
> Hi,
> If the database size is small just backup and write in CD or TAPE.
> and Restore it on the client side.
> Also backup the master,msdb and model database.
> from
> Doller
>
Why would you backup Master, MSDB and Model in this case? I'd just
backup the user database and restore it on the customers server.
Another option could be to script out the database etc. and then run
this script on the customers server.
Regards
Steen
|||We use SQL scripts rather than backup/restore or detach/attach. This
provides both new and upgrade install functionality and works well since we
also keep or DDL scripts under source control.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||These may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
|||I also prefer scripts instead of a binary copy of the database. Among other things, when a binary
copy is distributed, you need to make 100% certain that the app work with different collation on the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> We use SQL scripts rather than backup/restore or detach/attach. This provides both new and
> upgrade install functionality and works well since we also keep or DDL scripts under source
> control.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
>
|||hi Gareth,
Gareth Sharp wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which
> group would be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know
> what they all are.
> But, how do I move our database from our server, onto our customers
> server?
I'm with Dan and Tibor...
try having a look at
http://msdn.microsoft.com/msdnmag/is...baseinstaller/ ...
it's a good article and implies support for versioning and source control..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||And you need to remap users to logins on the new server as well when you use
a backup, because the mapping uses the SID. Scripts will just generate the
user and map it to the login without using a autogenerated ID.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMy3dGAzFHA.3856@.tk2msftngp13.phx.gbl...
>I also prefer scripts instead of a binary copy of the database. Among other
>things, when a binary copy is distributed, you need to make 100% certain
>that the app work with different collation on the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>

Database deployment

Hi there
Firstly, apologies for the multi-post - I could not decide which group would
be best to post this to.
I have a simple (I think) question.
We are about to roll out our first SQL server based application. The
application and run times are no problems - I'm pretty sure I know what they
all are.
But, how do I move our database from our server, onto our customers
server?
Thanks
Gareth Sharpyou have a couple of option, two that pop up into my head (this early) are
backup and restore
detach and attach
http://sqlservercode.blogspot.com/
"Gareth Sharp" wrote:

> Hi there
> Firstly, apologies for the multi-post - I could not decide which group wou
ld
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what th
ey
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
>|||Hi,
If the database size is small just backup and write in CD or TAPE.
and Restore it on the client side.
Also backup the master,msdb and model database.
from
Doller|||doller wrote:
> Hi,
> If the database size is small just backup and write in CD or TAPE.
> and Restore it on the client side.
> Also backup the master,msdb and model database.
> from
> Doller
>
Why would you backup Master, MSDB and Model in this case? I'd just
backup the user database and restore it on the customers server.
Another option could be to script out the database etc. and then run
this script on the customers server.
Regards
Steen|||We use SQL scripts rather than backup/restore or detach/attach. This
provides both new and upgrade install functionality and works well since we
also keep or DDL scripts under source control.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>|||These may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>|||I also prefer scripts instead of a binary copy of the database. Among other
things, when a binary
copy is distributed, you need to make 100% certain that the app work with di
fferent collation on the
system databases.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> We use SQL scripts rather than backup/restore or detach/attach. This prov
ides both new and
> upgrade install functionality and works well since we also keep or DDL scr
ipts under source
> control.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message news:di5bpa$1g3$1$8300d
ec7@.news.demon.co.uk...
>|||hi Gareth,
Gareth Sharp wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which
> group would be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know
> what they all are.
> But, how do I move our database from our server, onto our customers
> server?
I'm with Dan and Tibor...
try having a look at
http://msdn.microsoft.com/msdnmag/i...abaseinstaller/ ...
it's a good article and implies support for versioning and source control..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||And you need to remap users to logins on the new server as well when you use
a backup, because the mapping uses the SID. Scripts will just generate the
user and map it to the login without using a autogenerated ID.
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMy3dGAzFHA.3856@.tk2msftngp13.phx.gbl...
>I also prefer scripts instead of a binary copy of the database. Among other
>things, when a binary copy is distributed, you need to make 100% certain
>that the app work with different collation on the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>

Database deployment

Hi there
Firstly, apologies for the multi-post - I could not decide which group would
be best to post this to.
I have a simple (I think) question.
We are about to roll out our first SQL server based application. The
application and run times are no problems - I'm pretty sure I know what they
all are.
But, how do I move our database from our server, onto our customers
server?
Thanks
Gareth Sharpyou have a couple of option, two that pop up into my head (this early) are
backup and restore
detach and attach
http://sqlservercode.blogspot.com/
"Gareth Sharp" wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>
>|||Hi,
If the database size is small just backup and write in CD or TAPE.
and Restore it on the client side.
Also backup the master,msdb and model database.
from
Doller|||doller wrote:
> Hi,
> If the database size is small just backup and write in CD or TAPE.
> and Restore it on the client side.
> Also backup the master,msdb and model database.
> from
> Doller
>
Why would you backup Master, MSDB and Model in this case? I'd just
backup the user database and restore it on the customers server.
Another option could be to script out the database etc. and then run
this script on the customers server.
Regards
Steen|||We use SQL scripts rather than backup/restore or detach/attach. This
provides both new and upgrade install functionality and works well since we
also keep or DDL scripts under source control.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>|||These may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
> Hi there
> Firstly, apologies for the multi-post - I could not decide which group
> would
> be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know what
> they
> all are.
> But, how do I move our database from our server, onto our customers
> server?
> Thanks
> Gareth Sharp
>|||I also prefer scripts instead of a binary copy of the database. Among other things, when a binary
copy is distributed, you need to make 100% certain that the app work with different collation on the
system databases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
> We use SQL scripts rather than backup/restore or detach/attach. This provides both new and
> upgrade install functionality and works well since we also keep or DDL scripts under source
> control.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
>> Hi there
>> Firstly, apologies for the multi-post - I could not decide which group would
>> be best to post this to.
>> I have a simple (I think) question.
>> We are about to roll out our first SQL server based application. The
>> application and run times are no problems - I'm pretty sure I know what they
>> all are.
>> But, how do I move our database from our server, onto our customers
>> server?
>> Thanks
>> Gareth Sharp
>>
>|||hi Gareth,
Gareth Sharp wrote:
> Hi there
> Firstly, apologies for the multi-post - I could not decide which
> group would be best to post this to.
> I have a simple (I think) question.
> We are about to roll out our first SQL server based application. The
> application and run times are no problems - I'm pretty sure I know
> what they all are.
> But, how do I move our database from our server, onto our customers
> server?
I'm with Dan and Tibor...
try having a look at
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/ ...
it's a good article and implies support for versioning and source control..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply|||And you need to remap users to logins on the new server as well when you use
a backup, because the mapping uses the SID. Scripts will just generate the
user and map it to the login without using a autogenerated ID.
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMy3dGAzFHA.3856@.tk2msftngp13.phx.gbl...
>I also prefer scripts instead of a binary copy of the database. Among other
>things, when a binary copy is distributed, you need to make 100% certain
>that the app work with different collation on the system databases.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:Oj9tlvzyFHA.3720@.TK2MSFTNGP14.phx.gbl...
>> We use SQL scripts rather than backup/restore or detach/attach. This
>> provides both new and upgrade install functionality and works well since
>> we also keep or DDL scripts under source control.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Gareth Sharp" <gs@.paxsoft.co.uk> wrote in message
>> news:di5bpa$1g3$1$8300dec7@.news.demon.co.uk...
>> Hi there
>> Firstly, apologies for the multi-post - I could not decide which group
>> would
>> be best to post this to.
>> I have a simple (I think) question.
>> We are about to roll out our first SQL server based application. The
>> application and run times are no problems - I'm pretty sure I know what
>> they
>> all are.
>> But, how do I move our database from our server, onto our customers
>> server?
>> Thanks
>> Gareth Sharp
>>
>>
>