Dear all,
One of our application consist of configuring an SQL server database with
user rights to tables and preparing corresponding store procedure.
usually when we do this, we prepare the server in our test environment and
then ship the whole hardware pre configure to customers.
in some cases we do not have the customer server, so we need to do the whole
configuration on site and in cas of errors its hard to find.
is there an easy way to prepare the SQL confisuration database, store
procedure, table rights on a test PC, then do a kind of straight forward
deployement on any server running SQL ?
thanks for your help
regards
SergeHi,
You can create the customer database in your local sql server. Do all the
setups in that new database. backup the database or detach the database.
Copy the backup or MDF and LDF file and take it in a tape. Restore the files
from tape to Customer side server and
do a Database restore or Attach the MDF and LDF.
Incase if you have login an user suid mismacth use the system stored
procedure sp_change_users_login to fix.
This will ensure that all the configurations are avaiable.
Thanks
Hari
SQL Server MVP
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I have seen the method suggested earlier about taking the mdf and ldf and
using sp_attach_Db and in most cases you should be OK ( except for
permissions.) However the risk you are taking is with the file version...
The physical files will only have a limited life span, as MS changes things
usually attach works for one release backwards...
Another way is to generate scripts... These take longer to run, but are
usually safer because basic syntax does not change that often...One thing
you might do is combine the two... In your release, generate the scripts.
Then when you are about to go and setup a db for a user, THEN run the
scripts, and take the resulting mdf and ldf...
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
"serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> Dear all,
> One of our application consist of configuring an SQL server database with
> user rights to tables and preparing corresponding store procedure.
> usually when we do this, we prepare the server in our test environment and
> then ship the whole hardware pre configure to customers.
> in some cases we do not have the customer server, so we need to do the
> whole
> configuration on site and in cas of errors its hard to find.
> is there an easy way to prepare the SQL confisuration database, store
> procedure, table rights on a test PC, then do a kind of straight forward
> deployement on any server running SQL ?
> thanks for your help
> regards
> Serge|||I am a bit confuse as I am new in that part.
let say that my ServerA is my test server in my office on which I prepare
every thing
lets call ServerB the target server on which th final DB will run
Could you please details me which operation to do on which server?
What is the content of those MDF and LDF files ?
How to deploy rights or group from server A to ServerB at the same time
thanks and sorry to disturb
experince will come with the time :-)
serge
"Wayne Snyder" wrote:
> I have seen the method suggested earlier about taking the mdf and ldf and
> using sp_attach_Db and in most cases you should be OK ( except for
> permissions.) However the risk you are taking is with the file version...
> The physical files will only have a limited life span, as MS changes things
> usually attach works for one release backwards...
> Another way is to generate scripts... These take longer to run, but are
> usually safer because basic syntax does not change that often...One thing
> you might do is combine the two... In your release, generate the scripts.
> Then when you are about to go and setup a db for a user, THEN run the
> scripts, and take the resulting mdf and ldf...
>
> --
> 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
> "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > Dear all,
> >
> > One of our application consist of configuring an SQL server database with
> > user rights to tables and preparing corresponding store procedure.
> >
> > usually when we do this, we prepare the server in our test environment and
> > then ship the whole hardware pre configure to customers.
> >
> > in some cases we do not have the customer server, so we need to do the
> > whole
> > configuration on site and in cas of errors its hard to find.
> >
> > is there an easy way to prepare the SQL confisuration database, store
> > procedure, table rights on a test PC, then do a kind of straight forward
> > deployement on any server running SQL ?
> >
> > thanks for your help
> > regards
> > Serge
>
>|||On ServerA you will prepare the database to be installed, call it DBa. Once
this is complete you have 2 choices. 1) You can dump DBa and use the
resulting dump file, or 2) you can detach DBa and use the MDF and LDF files.
Copy either the dump file or MDF and LDF files to ServerB.
If you selected the database dump option, restore DBa. See Books Online for
the syntax.
If you selected the detach option, attach DBa. Again see Books Online for
the syntax.
As mentioned in one of the earlier posts if you created any data server
logins and database users you will need to fix those up via the
sp_change_users_login stored proc. See Books Online for documentation.
"serge calderara" wrote:
> I am a bit confuse as I am new in that part.
> let say that my ServerA is my test server in my office on which I prepare
> every thing
> lets call ServerB the target server on which th final DB will run
> Could you please details me which operation to do on which server?
> What is the content of those MDF and LDF files ?
> How to deploy rights or group from server A to ServerB at the same time
> thanks and sorry to disturb
> experince will come with the time :-)
> serge
> "Wayne Snyder" wrote:
> > I have seen the method suggested earlier about taking the mdf and ldf and
> > using sp_attach_Db and in most cases you should be OK ( except for
> > permissions.) However the risk you are taking is with the file version...
> > The physical files will only have a limited life span, as MS changes things
> > usually attach works for one release backwards...
> >
> > Another way is to generate scripts... These take longer to run, but are
> > usually safer because basic syntax does not change that often...One thing
> > you might do is combine the two... In your release, generate the scripts.
> > Then when you are about to go and setup a db for a user, THEN run the
> > scripts, and take the resulting mdf and ldf...
> >
> >
> > --
> > 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
> >
> > "serge calderara" <sergecalderara@.discussions.microsoft.com> wrote in
> > message news:EDEAF3C8-903B-49D6-8A48-9FD1B71E379F@.microsoft.com...
> > > Dear all,
> > >
> > > One of our application consist of configuring an SQL server database with
> > > user rights to tables and preparing corresponding store procedure.
> > >
> > > usually when we do this, we prepare the server in our test environment and
> > > then ship the whole hardware pre configure to customers.
> > >
> > > in some cases we do not have the customer server, so we need to do the
> > > whole
> > > configuration on site and in cas of errors its hard to find.
> > >
> > > is there an easy way to prepare the SQL confisuration database, store
> > > procedure, table rights on a test PC, then do a kind of straight forward
> > > deployement on any server running SQL ?
> > >
> > > thanks for your help
> > > regards
> > > Serge
> >
> >
> >
