Best Practices?

Hi,

I am installing SQL 2005 in a cluster env and there is a section which asks you about the domina\group for each of the accounts. I am going to have 3 virtual server so does it mean I should have 3*3 structure.

Can anyone tell me what exactly the group does and can there be 3 groups with 9 accounts.

What is the best practices
Thanks

I would just create one Windows Group with three different user accounts, then use one account for all of the services on each instance.

Best Practices?

Hi,

I am installing SQL 2005 in a cluster env and there is a section which asks you about the domina\group for each of the accounts. I am going to have 3 virtual server so does it mean I should have 3*3 structure.

Can anyone tell me what exactly the group does and can there be 3 groups with 9 accounts.

What is the best practices
Thanks

I would just create one Windows Group with three different user accounts, then use one account for all of the services on each instance.

Best practices: GROUP BY clause

I was wondering what the best way to write a GROUP BY clause when there are many (and time consuming) operations in the fields by grouped.

Fictious example:

SELECT DeptNo, AVG(Salary) FROM Department GROUP BY DeptNo;

This will give me the average salary per department. Let's say, however that
I had 10-15 fields being returned (along with the AVG(Salary)) and some fields even had operations being performed on them. Is it better to create a temporary table to calculate the sum per department (or a VIEW) and then
perform a JOIN with the rest of the data?

Fictious example:

SELECT DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField), AVG(Salary)
GROUP BY DATENAME(y, StartDate), DATENAME(m, StartDate), DATEPART(d, StartDate), SUBSTR(DeptName, 1, 10), SomeFunction(SomeField), SomeFunction(SomeField);

Am I better off writing my query this way or using a JOIN on some temporary table or view?

ThanksWrite your query this way. One way to maximize the efficiency of a process is to reduce the number of times the server has to scan through the data. By putting all your aggregate functions in a single statement, the server only needs to run through the dataset one time.

But are all those datename and datepart functions necessary? That seems kind of wastefull. You could accomplish the same thing just by sorting by date.|||Bindman...That was just an example I made up. I am not asking this for a particular case right now, but I have in the past had queries that had many fields, and many of those fields had math/string/etc functions performed on them. Most of the time This was to provide formatting for a query that would be dumped into a report or out put to the user. For example, I might format an ID by Left padding with zeroes:
RIGHT(REPLICATE(MyPadChar, MyFieldWidth) + CAST(MyID AS VARCHAR), MyFieldWidth) AS [MyFormattedID]......

so all those fields would appear in my group by clause....I was wondering if this was a good practice.

Thanks|||It's acceptable in my opinion.

Anybody else want to comment on this?|||SELECT * FROM (
SELECT DATENAME(y, StartDate) AS Col1
, DATENAME(m, StartDate) AS Col2
, DATEPART(d, StartDate) AS Col3
, SUBSTRING(DeptName, 1, 10) AS Col4
, SomeFunction(SomeField) AS Col5
, SomeFunction(SomeField) AS Col6
, AVG(Salary) AS Col7
FROM myTable99) AS XXX
GROUP BY Col1, Col2, Col3, Col4, Col5, Col6|||Yeah, I thought about suggesting that. I've used it for clarity of coding before, but can you think of any reason it might or might not be more efficient? I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?|||Kaiser. Thanks for the hint...that'll clean things up a whole lot...
As for blindman, yeah, I'd like to know if evaluation takes place twice when the quesy is run.|||I guess the question is, when you include a formula in the output and also specify it in the GROUP BY clause, does the server calculate the formula twice, or is it smart enough to just calculate it once?

It had better be once, since it's a derived table...I never checked, but a SHOWPLAN should tell you what's up.

But again, this is M$, so you never know...

But since I'm a betting kinda guy...

$US1000.00 on Once to Win....|||Kaiser. Thanks for the hint....

Your welcome....Kaiser? Try Brett, x002548, or Ski (get it...)

Where are you in the world?|||Then would you agree that your subquery example would not be more efficient than coding formulas in the WHERE clause, though it scores points for clarity?|||No...would you agree that using formulas would cause non sargable predicates there by invalidating the use of any index?|||Yes, but the use of indexes is lost anyway when you filter on the results of forumulas in the subquery. I don't see how either of these methods would make efficient use of indexes.|||In the subquery, Query Analyser still complains about the subquery you mentioned. If it sees an aggregate function with other fields, you have to GROUP BY the other fields.
Sorry, I read it too fast the first time. I thought I saw:

SELECT ClientID AS Col1, OrderID AS Col2, SUM(Price) AS Col3
GROUP BY Col1, Col2;

I did not see the subquery and thought that what you gave me was similiar to
the ORDER BY 1, 2, 3... clause (instead of using actual column names)|||scores points for clarity? are you talking about post #5?

yes, assuming all the errors were fixed up!|||Yeah, I meant post #5. (the thread is getting a little long).

Um, the subquery won't run because of the aggregate missing a GROUP BY clause.

I also couldn't get the group clause to work on a column alias...|||select col1, col2, col3, col4, col5, col6, col7
from (
select datename(y, startdate) as col1
, datename(m, startdate) as col2
, datepart(d, startdate) as col3
, substring(deptname, 1, 10) as col4
, somefunction(somefield) as col5
, somefunction(somefield) as col6
, avg(salary) as col7
from mytable99
group
by datename(y, startdate)
, datename(m, startdate)
, datepart(d, startdate)
, substring(deptname, 1, 10)
, somefunction(somefield)
, somefunction(somefield)
) as xxx

Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting to
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next Report].
To print a report you should use the [Copy] option and then paste it ona a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:
> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> is installed. The installation went fine. I created a report by connecting to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the software
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> To print a report you should use the [Copy] option and then paste it ona a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
> > Before hand, I Apologize if I am posting this on a wrong group.
> >
> > I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> >
> > http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> >
> > on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> > is installed. The installation went fine. I created a report by connecting to
> > a server. Now, I want to print the report but I can't.
> >
> > The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> > able to print report by clicking on the "print report" icon on "Compliance
> > Reports" page but THERE IS NO "PRINT REPORT" icon.
> >
> > Am I dreaming ? or what document says really doesn't match what the software
> > does ? or is this a limitation on Windows XP ?
> >
> > Thanks for any help.|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I use
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
> > Hi DXC:
> > you should have just four choices on the report form:
> > [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> > To print a report you should use the [Copy] option and then paste it ona a
> > Word document or on any other editor you like.
> > This action let you generate a document containig the reèportsummary. to
> > Print the details you should copy any of them (or just the details you are
> > interested for) one at a time.
> >
> > "DXC" wrote:
> >
> > > Before hand, I Apologize if I am posting this on a wrong group.
> > >
> > > I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> > >
> > > http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> > >
> > > on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> > > is installed. The installation went fine. I created a report by connecting to
> > > a server. Now, I want to print the report but I can't.
> > >
> > > The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> > > able to print report by clicking on the "print report" icon on "Compliance
> > > Reports" page but THERE IS NO "PRINT REPORT" icon.
> > >
> > > Am I dreaming ? or what document says really doesn't match what the software
> > > does ? or is this a limitation on Windows XP ?
> > >
> > > Thanks for any help.

Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting to
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.
Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next Report].
To print a report you should use the [Copy] option and then paste it ona a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:

> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" from
> http://www.microsoft.com/downloads/details.aspx?FamilyID=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
> is installed. The installation went fine. I created a report by connecting to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the software
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.
|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Next Report].
> To print a report you should use the [Copy] option and then paste it ona a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
[vbcol=seagreen]
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I use
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
sql

Best Practices Analyzer

Before hand, I Apologize if I am posting this on a wrong group.
I have downloaded and installed "SQL Server 2000 Best Practices Analyzer" fr
om
http://www.microsoft.com/downloads/...&displaylang=en
on to my Windows XP machine where I have "SQL Server 2000 Personal addition"
is installed. The installation went fine. I created a report by connecting t
o
a server. Now, I want to print the report but I can't.
The document "MSBPA_User_Guide.doc" that comes with it says that I should be
able to print report by clicking on the "print report" icon on "Compliance
Reports" page but THERE IS NO "PRINT REPORT" icon.
Am I dreaming ? or what document says really doesn't match what the software
does ? or is this a limitation on Windows XP ?
Thanks for any help.Hi DXC:
you should have just four choices on the report form:
[Remove Report], [Copy Report], [Previous Report] and [Next
Report].
To print a report you should use the [Copy] option and then paste it ona
a
Word document or on any other editor you like.
This action let you generate a document containig the reèportsummary. to
Print the details you should copy any of them (or just the details you are
interested for) one at a time.
"DXC" wrote:

> Before hand, I Apologize if I am posting this on a wrong group.
> I have downloaded and installed "SQL Server 2000 Best Practices Analyzer"
from
> http://www.microsoft.com/downloads/...&displaylang=en
> on to my Windows XP machine where I have "SQL Server 2000 Personal additio
n"
> is installed. The installation went fine. I created a report by connecting
to
> a server. Now, I want to print the report but I can't.
> The document "MSBPA_User_Guide.doc" that comes with it says that I should
be
> able to print report by clicking on the "print report" icon on "Compliance
> Reports" page but THERE IS NO "PRINT REPORT" icon.
> Am I dreaming ? or what document says really doesn't match what the softwa
re
> does ? or is this a limitation on Windows XP ?
> Thanks for any help.|||Thanks Gilberto.........I wanted to print details also but If I have to
print one by one then I will not print.........Is it any better if I use
the "SQL Server Reporting Services" ?
Thanks again.
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> Hi DXC:
> you should have just four choices on the report form:
> [Remove Report], [Copy Report], [Previous Report] and [Nex
t Report].
> To print a report you should use the [Copy] option and then paste it o
na a
> Word document or on any other editor you like.
> This action let you generate a document containig the reèportsummary. to
> Print the details you should copy any of them (or just the details you are
> interested for) one at a time.
> "DXC" wrote:
>|||To use Reporting services you need to save the report details in a compliant
data source: anyway you have to peek each detail you are interested to.
Surely you can obtain a much better result in terms of presentation and
usability.
"DXC" wrote:
[vbcol=seagreen]
> Thanks Gilberto.........I wanted to print details also but If I have to
> print one by one then I will not print.........Is it any better if I us
e
> the "SQL Server Reporting Services" ?
> Thanks again.
> "Gilberto Zampatti" wrote:
>

Best Practices - Naming conventions

I work with a group of developers that insist their application's success depends on being able to create databases and tables on the fly. Their proposed naming convention is databases (job number) and for tables (job number "_" order number. I tried to explain this plan eludes any "normal" notion of database sanctity or sanity and giving out DDL Admin rights to a .Net component is a bad idea, all to no avail. Anyone out there with an opinion either way or am I taking my title (DBA) too seriously?To create databases and tables on the fly !!!!

Good idea! But who will be in charge for supporting this?
DBA or Developer? I guess anyone knows answer. On fly it is possible to create 'temporary' permanent objects - and remove them by using some rules. Use script for creating DB from application.|||It sounds to me like your developers don't understand the concept of scalability, or they wouldn't need to be creating database on the fly for different job numbers, or different tables for different orders. It sounds absurd, and rather amateur to me (you asked for opinions, right?).

Another 1 or 2 dimensions in their tables would handle it.

blindman|||Sounds like you just caught the SharePoint Team Services bug. Microsoft brags about a server with over 1,000 databases on it. Just tell the developers (and management) that you can not really be responsible for the performance on this machine, since you can not predict disk usage, or reallocate any files anywhere about, because they just keep coming back. If you have one disk fill up, then all you will get is errors from your application. God help you, if you catch a runaway transaction that blows your transaction logs beyond the bounds of normal space, too.

Just remember, just because Microsoft tells you you can do something, doesn't mean you should do it.|||Sound like MCrowley already "been there, done that" like I am in deep
caca with Sharepoint right now. They created DB "on the fly" like rabbit running high on Viagra... The DATA & LOG files default setup in the SQL Server's Properties won't apply to these Sharepoint DB creation (only effective when using GUI to create DB) . The only concept of scalability they can understand is getting higher & higher... :-)|||Sounds to me that your developers just re-invented the concept of partioned tables; however, not really clever. Temporary databases should not the way to do it! Could you get some more deeper into details of your developer's intentions? I'm sure we all here can propose much more cleverer ways to achieve the same.

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
Derek
Derek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexed
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>
|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60EE-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:

Best method to transfer data

Hi Group,
I just started at a company and am trying to come up with a solution
to streamline the datawarehouse.
The problem is, we have two databases. Database1 (548 tables) is
generated from user input and we cannot control the schema. Database2
(40 tables) is a staging DB that optimally will contain some of the
Creates and Updates from the previous day from within Database1.
Database2 is built from a conglomeration of tables in Database1,
therefore we have created 40 views which encapsulates data from
multiple tables in Database1 and are using DTS to call these views and
populate Database2 with a snapshot.
There are 2 problems with the above setup. First is, we do not need
to take an entire snapshot of the views to populate Database2, we only
need the previous days changes (the DB is growing and we cannot afford
it). Second, DTS is a pain because we are using a separate view for
every table and a separate DTS package to copy every view to
Database2. Maintenance is tough.
Currently, we are investigating the use of triggers, but I think this
will end up being a maintenance nightmare also. Is there anyway to
use replication in conjunction with views to copy *only* the previous
days changes to the other Database? Or does anyone have any other
suggestions to the best way to set this up? *Any* insight or advice
on a better setup is welcome.
Thanks much,
DerekDerek,
I haven't set this up for a while, but transactional replication of indexed
views would seem to meet your requirements.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]|||Thanks very much Paul. Because of your suggestion, I am investigating
using this method.
I read that indexed views tax the system it runs on, so I'm looking at
using transactional replication to replicate the data to another box
which maintains the indexed views, then publish that data to the box
that needs it.
Thanks again,
Derek
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<OrKxNNUtEHA.1548@.TK2MSFTNGP
10.phx.gbl>...[vbcol=seagreen]
> Derek,
> I haven't set this up for a while, but transactional replication of indexe
d
> views would seem to meet your requirements.
> HTH,
> Paul Ibison (SQL Server MVP)
>|||Derek take a look at Trey Johnsons DTS Best Practices for Business
Intelligence white paper in msdn online it should steer you in the right
direction as far as coming up with a standard data capture methodology
"derek" wrote:

> Hi Group,
> I just started at a company and am trying to come up with a solution
> to streamline the datawarehouse.
> The problem is, we have two databases. Database1 (548 tables) is
> generated from user input and we cannot control the schema. Database2
> (40 tables) is a staging DB that optimally will contain some of the
> Creates and Updates from the previous day from within Database1.
> Database2 is built from a conglomeration of tables in Database1,
> therefore we have created 40 views which encapsulates data from
> multiple tables in Database1 and are using DTS to call these views and
> populate Database2 with a snapshot.
> There are 2 problems with the above setup. First is, we do not need
> to take an entire snapshot of the views to populate Database2, we only
> need the previous days changes (the DB is growing and we cannot afford
> it). Second, DTS is a pain because we are using a separate view for
> every table and a separate DTS package to copy every view to
> Database2. Maintenance is tough.
> Currently, we are investigating the use of triggers, but I think this
> will end up being a maintenance nightmare also. Is there anyway to
> use replication in conjunction with views to copy *only* the previous
> days changes to the other Database? Or does anyone have any other
> suggestions to the best way to set this up? *Any* insight or advice
> on a better setup is welcome.
> Thanks much,
> Derek
>|||Wow... About 95% of that article is over my head. I have a lot of
research to do. I was actually beginning to thing that SQL server was
limited in it's DataWarehousing. How wrong was I.
Thanks,
Derek
Richard S. Hale <RichardSHale@.discussions.microsoft.com> wrote in message news:<351947F4-60E
E-426D-9F94-D0EE55083C93@.microsoft.com>...[vbcol=seagreen]
> Derek take a look at Trey Johnsons DTS Best Practices for Business
> Intelligence white paper in msdn online it should steer you in the right
> direction as far as coming up with a standard data capture methodology
> "derek" wrote:
>

Benefit of multiple databases?

Hi !

We're designing our data model, and have found that we have two groups of tables (about 10 tables in each group). The tables within each group are dependent, but the two groups are independent of eachother.

Now, our two choices are:

1. Put all tables into one database.

2. Put the two groups into two separate databases.

For simplicity, option 1 is the winner. However, my question is, will there be noticeable peformance benefits by using two databases? (In which case, option 2 will be the winner).

Thanks,

Martin

There will be a memory overhead with have connections open to two databases, I would recommend using just a single SDF file for all your tables.

Beginners questions about Reporting Services Admin

I'm looking at the Properties tab of a report In the Report Manager. If I go
into 'New Role Assignment', I see a 'Group or user name:' textbox.
Do I understand this correctly?: Any name I enter for a new role must match
an existing SQL Server user or group? (If so I would expect some sort of
lookup/selection list, which is the main cause for my confusion.)No, this has nothing to do with SQL Server. RS is an asp.net application and
it uses roles to manage who gets to run a report, create subscriptions, etc.
Assuming that you are using integrated security (the default) then what you
are doing is assigning a user or group to a particular role. If you are in
the local administrators group for the server (not SQL Server, but the
server RS is running on) then you are automatically part of the Content
Manager role.
When you create a datasource then you deal with the credentials for
retrieving the data for the report.
So, two different things which is good. Remember, you can connect to many
different sources for the data for the reports and they can all be using
different credentials for retrieving that data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> I'm looking at the Properties tab of a report In the Report Manager. If I
> go
> into 'New Role Assignment', I see a 'Group or user name:' textbox.
> Do I understand this correctly?: Any name I enter for a new role must
> match
> an existing SQL Server user or group? (If so I would expect some sort of
> lookup/selection list, which is the main cause for my confusion.)|||Ok, admittedly I am not a security expert. I should have said Windows, not
SQL Server. Let me rephrase the question.
Are you saying in the New Role Assignment window, the name I enter in the
'Group or user name' box must match exactly an existing Windows group or
user?
(And again I say that I am rather confused that it is a textbox rather than
a selection list.)
"Bruce L-C [MVP]" wrote:
> No, this has nothing to do with SQL Server. RS is an asp.net application and
> it uses roles to manage who gets to run a report, create subscriptions, etc.
> Assuming that you are using integrated security (the default) then what you
> are doing is assigning a user or group to a particular role. If you are in
> the local administrators group for the server (not SQL Server, but the
> server RS is running on) then you are automatically part of the Content
> Manager role.
> When you create a datasource then you deal with the credentials for
> retrieving the data for the report.
> So, two different things which is good. Remember, you can connect to many
> different sources for the data for the reports and they can all be using
> different credentials for retrieving that data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> > I'm looking at the Properties tab of a report In the Report Manager. If I
> > go
> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
> >
> > Do I understand this correctly?: Any name I enter for a new role must
> > match
> > an existing SQL Server user or group? (If so I would expect some sort of
> > lookup/selection list, which is the main cause for my confusion.)
>
>|||Yes, you are mapping a Windows user/group to a RS role. What I do is I
create a local group on the box specifically for this. I add individual
users and domain groups to that local group. I then assign that group to a
role.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
news:6C2052BC-4ACE-4D8F-84A5-A9316D86F0B7@.microsoft.com...
> Ok, admittedly I am not a security expert. I should have said Windows,
> not
> SQL Server. Let me rephrase the question.
> Are you saying in the New Role Assignment window, the name I enter in the
> 'Group or user name' box must match exactly an existing Windows group or
> user?
> (And again I say that I am rather confused that it is a textbox rather
> than
> a selection list.)
> "Bruce L-C [MVP]" wrote:
>> No, this has nothing to do with SQL Server. RS is an asp.net application
>> and
>> it uses roles to manage who gets to run a report, create subscriptions,
>> etc.
>> Assuming that you are using integrated security (the default) then what
>> you
>> are doing is assigning a user or group to a particular role. If you are
>> in
>> the local administrators group for the server (not SQL Server, but the
>> server RS is running on) then you are automatically part of the Content
>> Manager role.
>> When you create a datasource then you deal with the credentials for
>> retrieving the data for the report.
>> So, two different things which is good. Remember, you can connect to many
>> different sources for the data for the reports and they can all be using
>> different credentials for retrieving that data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
>> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
>> > I'm looking at the Properties tab of a report In the Report Manager.
>> > If I
>> > go
>> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
>> >
>> > Do I understand this correctly?: Any name I enter for a new role must
>> > match
>> > an existing SQL Server user or group? (If so I would expect some sort
>> > of
>> > lookup/selection list, which is the main cause for my confusion.)
>>|||I'm still having problems. I was under the impression that access was
controlled solely by RS itself. I know you suggested created a dedicated
group. However, I started small, with a single network ID that had
absolutely no presence on this particular machine.
I created a server role consisting of all the 'View' options. I started by
assigning the test id this 'read-only' role at the site level and then worked
down to the report. (It appears that I have to assign security at all the
levels, site-folder-report, before the user can see the actual report. Is
that right?)
However, once the Test ID finally had access to the report and started it, I
got the error message: An error has occured during report processing.
Cannot create a connection to data source 'DataSource11'. For more
information about this error navigate to the report server on the local
server machine, or enable remote errors.'
I really can't find anything in the error logs that seems relevant and I'm
not sure I understand some of the other references to this problem on this
group. Any suggestions?
"Bruce L-C [MVP]" wrote:
> Yes, you are mapping a Windows user/group to a RS role. What I do is I
> create a local group on the box specifically for this. I add individual
> users and domain groups to that local group. I then assign that group to a
> role.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> news:6C2052BC-4ACE-4D8F-84A5-A9316D86F0B7@.microsoft.com...
> > Ok, admittedly I am not a security expert. I should have said Windows,
> > not
> > SQL Server. Let me rephrase the question.
> >
> > Are you saying in the New Role Assignment window, the name I enter in the
> > 'Group or user name' box must match exactly an existing Windows group or
> > user?
> >
> > (And again I say that I am rather confused that it is a textbox rather
> > than
> > a selection list.)
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No, this has nothing to do with SQL Server. RS is an asp.net application
> >> and
> >> it uses roles to manage who gets to run a report, create subscriptions,
> >> etc.
> >> Assuming that you are using integrated security (the default) then what
> >> you
> >> are doing is assigning a user or group to a particular role. If you are
> >> in
> >> the local administrators group for the server (not SQL Server, but the
> >> server RS is running on) then you are automatically part of the Content
> >> Manager role.
> >>
> >> When you create a datasource then you deal with the credentials for
> >> retrieving the data for the report.
> >>
> >> So, two different things which is good. Remember, you can connect to many
> >> different sources for the data for the reports and they can all be using
> >> different credentials for retrieving that data.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "B. Chernick" <BChernick@.discussions.microsoft.com> wrote in message
> >> news:18CD1BB0-50CD-422B-8A8B-2770B1B7CBFB@.microsoft.com...
> >> > I'm looking at the Properties tab of a report In the Report Manager.
> >> > If I
> >> > go
> >> > into 'New Role Assignment', I see a 'Group or user name:' textbox.
> >> >
> >> > Do I understand this correctly?: Any name I enter for a new role must
> >> > match
> >> > an existing SQL Server user or group? (If so I would expect some sort
> >> > of
> >> > lookup/selection list, which is the main cause for my confusion.)
> >>
> >>
> >>
>
>|||I am experiencing the same issue as B.Chernick.
Just as B.Chernick explains, I have also created a Test user that
access the report, but gets errors during processing.
However, I noticed that if I gave my Test user domain adminstrator
privileges (which is not possible in the production environment) I am
able to view the report without any difficulty. This leads me to
believe that there is a permissions issue someplace along the line.
Any ideas?

Beginner: How to group data?

Hello,
Thanks for reviewing my question. I am new to reporting services and
experimented with a couple of reports. I have the following data in a table:
# name slot
1 A1 1
1 B1 2
1 C1 3
2 A2 1
2 B2 2
2 C2 3
I would like to output in this fashison:
1 2
-- --
1 A1 A2
2 B1 B2
3 C1 C2
Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
group rows/columns even though all my data I need is in my table?
Many Thanks
PeterA follow up:
I am not familar with how the reporting mechanism groups the data. I have a
long list of data already contained in a table.
Current list
1 Book A Action
1 Book B Drama
2 Book C Novel
2 Book D Action
Desired output
1 2
Action Book A Book D
Drama Book B
Novel Book C
Can I output the data in the desired output or do I have to do queries with
cross joins or something of that sort?
Many Thanks
Peter
"Peter" wrote:
> Hello,
> Thanks for reviewing my question. I am new to reporting services and
> experimented with a couple of reports. I have the following data in a table:
> # name slot
> 1 A1 1
> 1 B1 2
> 1 C1 3
> 2 A2 1
> 2 B2 2
> 2 C2 3
> I would like to output in this fashison:
> 1 2
> -- --
> 1 A1 A2
> 2 B1 B2
> 3 C1 C2
> Should I be looking to use a Matrix objecjt? Do I need to use JOINs to
> group rows/columns even though all my data I need is in my table?
> Many Thanks
> Peter
>

Beginner question - sorry

Suppose I have a group of members and I want they to groups in to different categories. Each member has an ID and I would like each group to have an ID- I guess in programming it would typically done with a 2 dimensional array or something. How does SQL do this? Also each member may belong to more than one group. and also - I though of creating a new table for each group but then I am afraid of the maintenance of so many tables and the groups could quickly go into the thousands and ten thousands. And it really doesn't fit into my design plans also.

Any help or ideas would be appreciated.

Thanks,Hi,

There are a number of solutions to this problem, one would be to create a Groups table, a Users table and a UsersPerGroups table.

Users

UserID | UserName
--------
1 | echo88
2 | taylorza
3 | tmorton

Groups

GroupID | GroupName
--------
1 | Admin
2 | Power User
3 | User

Putting users in groups is as easy as populating the UsersPerGroups table, in this case
echo88 belongs to the Admin and Power User groups
taylorza belongs to the User group
tmorton belongs to no groups

UsersPerGroup

UserID | GroupID
-------
1 | 1
1 | 2
2 | 3

Hope this helps

BEFORE Triggers

Hey,
Why doesn't SQL Server support BEFORE triggers?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.orgHello,
Take a look into the INSTEAD OF Triggers in books online. Take a look into
the samples in below URL:-
http://www.sql-server-performance.com/nn_triggers.asp
Thanks
Hari
"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23zkCGhFYHHA.3656@.TK2MSFTNGP05.phx.gbl...
> Hey,
> Why doesn't SQL Server support BEFORE triggers?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:%23zkCGhFYHHA.3656@.TK2MSFTNGP05.phx.gbl...
> Hey,
> Why doesn't SQL Server support BEFORE triggers?
That's a question probably best asked of the development team.
The recommended solution is INSTEAD OF triggers.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Yes, I know about INSTEAD OF triggers. I've used them, and it's just
annoying that to emulate a BEFORE trigger, you have to rewrite the
insert/update statements in the trigger. And, if you change the columns of
the table, you have to rewrite the trigger.
I did not ask "how to;" I asked "why not?" I was hoping for a [MFST]
response.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eblGM6FYHHA.3256@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Take a look into the INSTEAD OF Triggers in books online. Take a look into
> the samples in below URL:-
> http://www.sql-server-performance.com/nn_triggers.asp
>
> Thanks
> Hari
> "Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
> news:%23zkCGhFYHHA.3656@.TK2MSFTNGP05.phx.gbl...
>> Hey,
>> Why doesn't SQL Server support BEFORE triggers?
>> --
>> Thank you,
>> Daniel Jameson
>> SQL Server DBA
>> Children's Oncology Group
>> www.childrensoncologygroup.org
>