Database Setup Tool

I was wondering if Microsoft SQL server has any tools that will will generate a setup.exe file to install the database on a seperate SQL server. I can see that you can script the entire database however it does not detect the dependencies in the correct order and hence I have to manually go through the script file and modify the creation order manually.

If we can generate a setup file

1) Network Admins who are installing the application and who have no idea of user SQL server can easily set up the database

2) I dont need to show the code for the sp, views etc as we can encrypt the code (I know it can be decrypted but that is ok)

I found tools from other vendors which we have to purchase to achive this.... Does MS Sql server have such a utility or is there any free utility that does this?

i guess u need to transfer database from one server to another. if this is the requirement , there are two methods

(a) Backup /restore

(b) detach/attach

You can automate it. If you have tried this pse let us know what is the difficulties u faced. Scripting database is not a complete solution.

pse refer BOL or post back if you are not clear with these terms

Madhu

|||

thanks Madhu.

But i only want to transfer the database structure ... not the data within it. If I do a backup / restore or detach / attach, it will take the data + transaction logs along with it. I can script the data + transaction logs to be cleared before doing the backup. But I dont want the data to be cleared in my environment as it has a lot of test data that has taken months to do. Alternatively i have to take another backup before clearing the data.

All this is going to be untidy. So that is why I was looking for a setup utility as I have seen other applications do this.

|||

if you asked the tool available ... then the answer may be Visio. Visio can easily handle these kind of scenario. As such there should not be any problem in Scripting from SQL Server also. Could you please tell us what is the problem u faced when u script the object using Management stuido and runing it in target server.

Redgate and all have tools for these kind of activities. But i strongly feel that SQL Server inbuilt features can easily handle this issue.

Also post back the result of Select @.@.version .

Madhu

Database setup script

I have MSDE installed on my machine as was wondering what the NETSDK is in the following commands.

@.rem Uncomment the following line for MSDE
@.rem set DBNAME=(local)\NETSDK
set DBNAME=(local)\NETSDK

Thanks,
Bob HIt would appear that NETSDK is the "instance name" of the MSDE installation.

With SQL Server 2000 (and MSDE), multiple instances of SQL Server can be installed. The first uses the "default instance"; that is, to access it, simply reference the name of the server in a connection string. To reference a non-default instance, follow the server with \<instance name>sql

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
Jason-- Jason wrote: --
>> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
--
Hi Jason,
This is a fairly trivial exercise. Have a look at the sample *.sql scripts that automatically creates the sample Northwind and Pubs database when you install SQL Server. They will give you some idea on how to automatically create a database and objects within that database.
The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs, respectively. The files can be found on your <drive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder
Hope this helps,
-Eric Cárdenas
SQL Server support|||You could, from your install program, either spawn OSQL with your script file (see Eric's post) as
input parameter. Or you can write your own app which reads the file and execute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statements inside your setup
programs source code.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
> I have very little experience in the realm of database setup & installation.
> Has anyone ever done this before and if so, what product/technology did you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
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
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"
database.
> I have very little experience in the realm of database setup &
installation.
> Has anyone ever done this before and if so, what product/technology did
you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...
> -- Jason wrote: --
> >> Hi Folks,
> > I've created a SQL Server database and converted it to Oracle (an app
we
> > made supports MSSQL and Oracle) and I'd like to be able to provide a
> > "server-side" installation that clients can run on their database
server
> > that would automatically create the MSSQL or Oracle database, the
tables,
> > index, relationships, etc. and insert the needed data for a "new"
database.
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts
that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.
> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,
respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder
> Hope this helps,
> -Eric Cárdenas
> SQL Server support
>

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
JasonYou can create scripts to do this. You can find similar
scripts from the installation of databases on SQL Server.
For an example, check the script that creates the Northwinds
database. The file is instnwnd.sql and it's located in the
install subdirectory under MSSQL, e.g.
C:\Program Files\Microsoft SQL Server\MSSQL\Install
-Sue
On Tue, 2 Dec 2003 14:35:53 -0800, "Jason"
<jasonmauss_nospam@.vsdotnetguru.com> wrote:

quote:

---

>Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app w
e
>made supports MSSQL and Oracle) and I'd like to be able to provide a
>"server-side" installation that clients can run on their database server
>that would automatically create the MSSQL or Oracle database, the tables,
>index, relationships, etc. and insert the needed data for a "new" database.
>I have very little experience in the realm of database setup & installation
.
>Has anyone ever done this before and if so, what product/technology did you
>use? I've been searching the newsgroups and haven't found much useful info
>on the topic. Am I looking in the wrong place?
>Thanks,
>Jason
>

---

|||You could, from your install program, either spawn OSQL with your script fil
e (see Eric's post) as
input parameter. Or you can write your own app which reads the file and exec
ute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statem
ents inside your setup
programs source code.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database
.
> I have very little experience in the realm of database setup & installatio
n.
> Has anyone ever done this before and if so, what product/technology did yo
u
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
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
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app

---

we

quote:

---

> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"

---

database.

quote:

---

> I have very little experience in the realm of database setup &

---

installation.

quote:

---

> Has anyone ever done this before and if so, what product/technology did

---

you

quote:

---

> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...

quote:

---

> -- Jason wrote: --
we[QUOTE]
server[QUOTE]
tables,[QUOTE]
database.[QUOTE]
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts

---

that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.

quote:

---

> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,

---

respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder

quote:

---

> Hope this helps,
> -Eric Crdenas
> SQL Server support
>

---

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database Setup and Design

I have a couple questions I hope someone might be able to answer, or
rather point me in the correct direction.

I am an independant developer and I am working on a small CRM for small
businesses. Nothing fancy, but built with c# and the .NET framework.

In my study of other CRM's like Microsoft, Clarify, and Misc I have
noticed something things that I do not understand.

1. When using a database, are customers and the customers
cases/problems kept in seperate tables?

2. When logging notes and misc, how is text formatted/kept in the
database. I see notes inside cases that are formatted and keep the
formatting after the cases are written the the DB.

Thanks for any info, and I am sure I am asking more then simple
questions.Sorry had one other thought. Say if customers are tracking emails or
phone messages, is a seperate table setup for each call/email or would
there be a notes database with a related key #.|||In a relational database a table represents an entity - informally, a set of
things that are alike in the sense that they have a common set of
attributes. So one might expect to see one table for Customers, another
table for Cases, a table for Invoices, etc. Data in tables are related by
keys. The database design will usually be static once it is built - the
design may be changed if the business requirements change but it isn't
necessary or desirable to create new tables at runtime. Database architects
use a set of rules called Normal Forms to help determine how to model
entities in a database.

In a nutshell, those are some general principles. However, my experience is
that commercial software packages built on relational databases sometimes
tend to use those databases in ways that are very non-standard and peculiar
to that application. The usual assumptions don't always apply. I don't know
about any of the packages you mentioned though so I could be wrong.

For storing formatted text there are various options. As RTF or HTML in a
text column for example. Or as a formatted document stored as a binary
database object.

Hope this helps.

--
David Portas
SQL Server MVP
--|||On 2/8/05 6:32 PM, in article
1107905552.527746.228500@.o13g2000cwo.googlegroups. com, "cvillard"
<cvillard@.gmail.com> wrote:

> I have a couple questions I hope someone might be able to answer, or
> rather point me in the correct direction.
> I am an independant developer and I am working on a small CRM for small
> businesses. Nothing fancy, but built with c# and the .NET framework.
> In my study of other CRM's like Microsoft, Clarify, and Misc I have
> noticed something things that I do not understand.
> 1. When using a database, are customers and the customers
> cases/problems kept in seperate tables?
> 2. When logging notes and misc, how is text formatted/kept in the
> database. I see notes inside cases that are formatted and keep the
> formatting after the cases are written the the DB.
> Thanks for any info, and I am sure I am asking more then simple
> questions.

We have a several applications where we store the data with HTML tags so it
displays formatted on the web site.

-Greg|||Thank you both for the information, this is really helpful and I think
I have some good information and ideas to start with. Much Appreciated.

Thanks again,
Chucksql

Database Role & Application Roles

In my environment we have all database roles..
Where we can setup application roles and can it will be more secure then
database roles.. will it be easy to manage,,Hi,
Application Roles:This is the best method for controlling user activities
regardless of the application used to communicate
with SQL Server
With the use of application roles you can restrict the users the usage of
Enterprise manager and Query Analyzer.
Say for your application to run you need give INSERT/DELETE and UPDATE
previlages to a user, if you
gave those previlages to user then he can login to Query analyzer and do any
thing on tables.
To overcome these you can assign all the previlages to a app role and enable
the app role inside the application.
App role will get enabled only by providing the right password, which is
defined inside the application. So even
if the user login using query analyzer he cant do any thing.
Thanks
Hari
SQL Server, MVP
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:5A9F6EEA-F56F-4598-AEDC-E17374F80BEE@.microsoft.com...
> In my environment we have all database roles..
> Where we can setup application roles and can it will be more secure then
> database roles.. will it be easy to manage,,

Database 'ReportServer' does not exist

Shouldn't reporting services setup create ReportServer database
automatically?
The setup fails at the very end with this message:
SQL Server failed to execute command for server configuration. The error
was: Database 'ReportServer' does not exist. Check sysdatabases. ...
Thanks,
-StanYes, Reporting Services install should create the ReportServer and
ReportServerTempDB databases, as well as the AdventureWorks2000 sample
database if you specified that feature to be installed. Be sure that you
have permissions to create the database. Also check the Reporting Services
install log, it may have more information about the error you are receiving.
Jonathan Kyle, MCSD
Microsoft WebData Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Stan" <nospam@.yahoo.com>
| Subject: Database 'ReportServer' does not exist
| Date: Thu, 16 Sep 2004 16:24:41 -0400
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 12.148.36.131
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.reportingsvcs:29431
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Shouldn't reporting services setup create ReportServer database
| automatically?
|
| The setup fails at the very end with this message:
|
| SQL Server failed to execute command for server configuration. The error
| was: Database 'ReportServer' does not exist. Check sysdatabases. ...
|
| Thanks,
|
| -Stan
|
|
||||I have seen this error if there is not default directory set for SQL server
to create databases in. You can check this via Enterprise manager go to the
properties on the server node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> Yes, Reporting Services install should create the ReportServer and
> ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> database if you specified that feature to be installed. Be sure that you
> have permissions to create the database. Also check the Reporting
Services
> install log, it may have more information about the error you are
receiving.
> Jonathan Kyle, MCSD
> Microsoft WebData Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> | From: "Stan" <nospam@.yahoo.com>
> | Subject: Database 'ReportServer' does not exist
> | Date: Thu, 16 Sep 2004 16:24:41 -0400
> | Lines: 13
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: 12.148.36.131
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.reportingsvcs:29431
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Shouldn't reporting services setup create ReportServer database
> | automatically?
> |
> | The setup fails at the very end with this message:
> |
> | SQL Server failed to execute command for server configuration. The
error
> | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> |
> | Thanks,
> |
> | -Stan
> |
> |
> |
>|||Yes, I ran out of space on SQL Server C: driver and setup failed to create a
reporting database
Thanks!
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%233TUfQ3nEHA.592@.TK2MSFTNGP11.phx.gbl...
> I have seen this error if there is not default directory set for SQL
server
> to create databases in. You can check this via Enterprise manager go to
the
> properties on the server node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jonathan Kyle [MSFT]" <jkyle@.online.microsoft.com> wrote in message
> news:5Pan3rynEHA.4040@.cpmsftngxa06.phx.gbl...
> > Yes, Reporting Services install should create the ReportServer and
> > ReportServerTempDB databases, as well as the AdventureWorks2000 sample
> > database if you specified that feature to be installed. Be sure that
you
> > have permissions to create the database. Also check the Reporting
> Services
> > install log, it may have more information about the error you are
> receiving.
> >
> > Jonathan Kyle, MCSD
> > Microsoft WebData Support Engineer
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > --
> > | From: "Stan" <nospam@.yahoo.com>
> > | Subject: Database 'ReportServer' does not exist
> > | Date: Thu, 16 Sep 2004 16:24:41 -0400
> > | Lines: 13
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> > | Message-ID: <OC#5asCnEHA.1236@.TK2MSFTNGP09.phx.gbl>
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: 12.148.36.131
> > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> > | Xref: cpmsftngxa06.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:29431
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | Shouldn't reporting services setup create ReportServer database
> > | automatically?
> > |
> > | The setup fails at the very end with this message:
> > |
> > | SQL Server failed to execute command for server configuration. The
> error
> > | was: Database 'ReportServer' does not exist. Check sysdatabases. ...
> > |
> > | Thanks,
> > |
> > | -Stan
> > |
> > |
> > |
> >
>

Database Replication

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:

> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Database performance very slow

Hello all,
I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
performance is no good at all..
how can I tune the performance better and make all connections faster'
Basically, i have 1G Ram, and it only have 130MB RAM available from
"Performance Monitor" as it should have 7XXMB memory when system started.
I am afraid that the database will more slow after replication of 10
databases are running...
Now, the replication is setup already, but no database will distribute.
Thanks in advanced.Hi
1GB memory is really small nowadays
"beachboy" <stanley@.javacatz.com> wrote in message
news:OYk%23gDRPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hello all,
> I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
> performance is no good at all..
> how can I tune the performance better and make all connections faster'
> Basically, i have 1G Ram, and it only have 130MB RAM available from
> "Performance Monitor" as it should have 7XXMB memory when system started.
> I am afraid that the database will more slow after replication of 10
> databases are running...
> Now, the replication is setup already, but no database will distribute.
> Thanks in advanced.
>

Database performance very slow

Hello all,
I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
performance is no good at all..
how can I tune the performance better and make all connections faster?
Basically, i have 1G Ram, and it only have 130MB RAM available from
"Performance Monitor" as it should have 7XXMB memory when system started.
I am afraid that the database will more slow after replication of 10
databases are running...
Now, the replication is setup already, but no database will distribute.
Thanks in advanced.
Hi
1GB memory is really small nowadays
"beachboy" <stanley@.javacatz.com> wrote in message
news:OYk%23gDRPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hello all,
> I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
> performance is no good at all..
> how can I tune the performance better and make all connections faster?
> Basically, i have 1G Ram, and it only have 130MB RAM available from
> "Performance Monitor" as it should have 7XXMB memory when system started.
> I am afraid that the database will more slow after replication of 10
> databases are running...
> Now, the replication is setup already, but no database will distribute.
> Thanks in advanced.
>

Database performance very slow

Hello all,
I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
performance is no good at all..
how can I tune the performance better and make all connections faster'
Basically, i have 1G Ram, and it only have 130MB RAM available from
"Performance Monitor" as it should have 7XXMB memory when system started.
I am afraid that the database will more slow after replication of 10
databases are running...
Now, the replication is setup already, but no database will distribute.
Thanks in advanced.Hi
1GB memory is really small nowadays
"beachboy" <stanley@.javacatz.com> wrote in message
news:OYk%23gDRPGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Hello all,
> I have setup a new Database Server (P4, 1G RAM, RAID 1), but i feel the
> performance is no good at all..
> how can I tune the performance better and make all connections faster'
> Basically, i have 1G Ram, and it only have 130MB RAM available from
> "Performance Monitor" as it should have 7XXMB memory when system started.
> I am afraid that the database will more slow after replication of 10
> databases are running...
> Now, the replication is setup already, but no database will distribute.
> Thanks in advanced.
>

database owner is needed

hi,

on 1 of my servers (actually, the dev. server I have setup up here at home), I must included the database owner everytime I select something or make a db call.

for example

select T.foo from K inner join T on T.id = K.id

must actualy be written like:

select dbo.T.foo from dbo.K inner join dbo.T on dbo.T.id = dbo.K.id

or else it wont work

what did I do wrong for this database owner thing to be a "must" when writing queries on this server.

spec:
sql 2000 sp4You are not connecting as a dbo user.|||

That's wierd. What error are you getting? It should check the dbo owned objects after checking for an object owned by you.

|||

Amir, please update thread.

Thanks,

Derek

|||sorry folks,

I was tied up on another project for the week. I came back and tried to

trouble shoot instead of wasting you guy's time but at the end, i

failed.
basically, here is the situation:

the actual server that will end up hosting the project is setup fine so if i leave out the dbo.* or user.* and just type select * from table it works.

right now, i'm resorting to including the dbo in my queries... and evertime i upload to the server, i do a search in that folder and replace " dbo." with "" in all files.

let me explain how i made this database:

originally, it was on a dev server somewhere in US...
i made the db on my home server. Then created a user and gave it admin privilages over that db.
Then i did "All Tasks > Import" and imported the tables from that server, to this new home db. I had to go back and manually select the primary key for each table, as they got lost during the transfer.

now, when i look at the "Server Explorer" cluster of tables in this new database, i see that they all have (dbo) beside their names, meaning the owner of each table by default is dbo.

i think that should pretty much cover everything....
any clue as to why user "must" be specified?|||

What do you get on the server that requires dbo. when you execute:

select suser_sname(), user_name()

Also, for some object where you have to enter dbo. for the object, execute:

select *
from sysobjects
where name = '<name>'

select *
from information_schema.tables
where table_name = '<name>'

Perhaps this will shed some light? Also what is in @.@.version?

This might just be a stumper that requires a higher power :)

|||

> What do you get on the server that requires dbo. when you execute:

>select suser_sname(), user_name()

| __|_
| foouser | foouser

>select *
>from sysobjects
>where name = '<name>'

irrelevant

> select *
> from information_schema.tables
> where table_name = '<name>'

TABLE_SCHEMA is 'dbo' for all objects

conclusion:
objects where created as dbo when they were imported from the server on the hosting company to the local staging environment;

how can i prevent this when using SQL 2000 enterprise manager for import

(note: from my exp. with sql 2005, this problem wasn't encountered when going through the same steps -- or rather similar steps -- using native sql 2005 import/export functionality within the SQL 2005 studio)


side note:
i rather find a script based answer rather learning to use the GUI, because of my DB2, mysql background -- obviously i'm not gifted when using GUI tools

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
Chris
For tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>

Database Options and their settings

Hi,
We usually just take the default settings for the model DB when we setup a
server (SQL2000 and SQL2005). This means that an option like
QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
either using a create SQL script or by restoring a database backup onto a
server.
Our connection defaults, when accessing from a client workstation or by
using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
these sessions has the create table statements does the table have the
QUOTED IDENTIFIER ON or OFF?
Thanks
ChrisFor tables, the setting doesn't apply but for something like
stored procedures, views, etc there are two set options that
will stay with the object and are based on what the settings
were of the connection that created the object:
ansi_nulls and quoted_identifier
-Sue
On Tue, 30 Jan 2007 15:07:09 -0700, "Chris Wood"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>We usually just take the default settings for the model DB when we setup a
>server (SQL2000 and SQL2005). This means that an option like
>QUOTED_IDENTIFIER would be set to OFF. We would then create a database by
>either using a create SQL script or by restoring a database backup onto a
>server.
>Our connection defaults, when accessing from a client workstation or by
>using SEM on SSMS on the server, have SET ANSI DEFAULTS ON. So if one of
>these sessions has the create table statements does the table have the
>QUOTED IDENTIFIER ON or OFF?
>Thanks
>Chris
>