Best strategy for multiple sites

I have a client that has a central database. Users are dispersed at regional
offices, and should only have access to data pertaining to their own office.
I'm trying to figure out the best way to secure data access for each group
of users. I thought of maintaining a table of users and the regional office
they belong to. Then a UDF would only return the appropriate data. This
means doing everything at the database level, regardless of the front end
(in this case, SSRS).
Another strategy would be to do all the filtering within SSRS, using the
user global variable, and filter the data dynamically based on the user
value (with a query expression). One thing I don't like about this approach
is that any other front end (web or Windows based) could access data without
filtering on a regional site basis.
Any thoughts?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comIt looks to me like you are currently planning to have a one to one match
between SQL Server logins and users. This can be a real pain. Also, you lose
the chance for connection pooling. For connection pooling to work the
connection must be exactly the same. If SQL Server can be run in mixed mode
I suggest creating a read only user that is used just for reporting. Then
when you have stored procedure you just have to give this execution rights.
Then combine that with using the user global variable. Don't filter it
locally, that will require a lot more data to be processed and you are much
better off to limit the amount of data coming to RS. Instead you can have a
hidden parameter that takes the user variable does a query and uses the
first aggregate to return the office. Then for all queries that need the
office you then pass that to the query.
Another possibility is you have a database per office. Then create views to
the database that has the data. Some views are one to one, others use the
appropriate office. Then use the user in an expression for the datasource.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23vUJ5XpNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>I have a client that has a central database. Users are dispersed at
>regional offices, and should only have access to data pertaining to their
>own office. I'm trying to figure out the best way to secure data access for
>each group of users. I thought of maintaining a table of users and the
>regional office they belong to. Then a UDF would only return the
>appropriate data. This means doing everything at the database level,
>regardless of the front end (in this case, SSRS).
> Another strategy would be to do all the filtering within SSRS, using the
> user global variable, and filter the data dynamically based on the user
> value (with a query expression). One thing I don't like about this
> approach is that any other front end (web or Windows based) could access
> data without filtering on a regional site basis.
> Any thoughts?
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Best Practices Question - how do you execute multiple packages?

I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.

Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.

Any comments or suggestions?

Sounds like you have a great solution that works for you. If you decided to use a parent package to execute the child packages, it would be easy enough to drive with a ForEach loop and a simple file input or even a script task. A lot about whether this is the right choice for you depends on some things you haven't told us. For example, what is the long term plan for your current solution, do you plan on enhancing the current solution etc. Also, if you use a parent package, you're not forced to use parent package configurations. You can still use the same configuration scheme you're using currently.

From the information you've given, I'd say that it sounds like a good solution.

|||

Thanks for the response, Kirk. My 200+ packages use the config file in an indirect manner. When I design my packages, I don't step thru the Configuration Wizard and create a direct configuration, I just make sure to always name my objects the same and then I just apply my global config file with the /CONFIGFILE "c:\wherever\conf.dtsConfig". However, the Execute Package Task doesn't have any properties for specifying configurations. Ideally, I'd like a master package that read a queue table and that table would have the path to a package and a path to a config file and feed that to the Execute Package Task. Also, it would be nice if the Execute Package Task had a property like /SET from dtexec.exe so you wouldn't have to have child packages "pulling" variables/data from a master package because you have to design child packages with an awareness that they are executing in a larger context. Being able to apply a change from a master package to a child package would be preferrabe.

Best practices for multiple report developers?

Two crystal reports developers are about to join me writing reports for
Reporting Services. Neither has used RS before and I don't believe either
has ever used Visual Studio.
I am concerned about what will happen when they build/deploy a project.
Will we end up with multiple unwanted folders and data sources? I thought
maybe we could all use copies of the same solution but I also want to use
SourceSafe and i dont want to worry about checking different versions in on
top of each other.
I would really like something like Visual InterDev 6 where multiple
developers connect their own solutions to a web application and all of the
resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a good
way to do this with the Report Designer?
Any thoughts or links regarding multi-developer coordination issues would be
greatly appreciated.I always use shared data sources. Once a shared data source is deployed it
is not overwritten unless you explicitly do so. When you deploy it will try
to deploy the data source, see that one is there and just give you a warning
that it was not deployed.
If you decide on the folder structure and name your projects the same, then
you should not have a problem (as long as you don't name the reports the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Al" <Al@.discussions.microsoft.com> wrote in message
news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> Two crystal reports developers are about to join me writing reports for
> Reporting Services. Neither has used RS before and I don't believe either
> has ever used Visual Studio.
> I am concerned about what will happen when they build/deploy a project.
> Will we end up with multiple unwanted folders and data sources? I thought
> maybe we could all use copies of the same solution but I also want to use
> SourceSafe and i dont want to worry about checking different versions in
> on
> top of each other.
> I would really like something like Visual InterDev 6 where multiple
> developers connect their own solutions to a web application and all of the
> resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> good
> way to do this with the Report Designer?
> Any thoughts or links regarding multi-developer coordination issues would
> be
> greatly appreciated.|||Is it possible for the Report Designer to "download" or otherwise learn about
the available shared data sources or is the only way to add the *.RDL file to
the project?
Thanks!
PS: Sorry for all the duplicates, I goofed up while trying to sign up for
passport at the same time as writing my question.
"Bruce L-C [MVP]" wrote:
> I always use shared data sources. Once a shared data source is deployed it
> is not overwritten unless you explicitly do so. When you deploy it will try
> to deploy the data source, see that one is there and just give you a warning
> that it was not deployed.
> If you decide on the folder structure and name your projects the same, then
> you should not have a problem (as long as you don't name the reports the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Al" <Al@.discussions.microsoft.com> wrote in message
> news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> > Two crystal reports developers are about to join me writing reports for
> > Reporting Services. Neither has used RS before and I don't believe either
> > has ever used Visual Studio.
> >
> > I am concerned about what will happen when they build/deploy a project.
> > Will we end up with multiple unwanted folders and data sources? I thought
> > maybe we could all use copies of the same solution but I also want to use
> > SourceSafe and i dont want to worry about checking different versions in
> > on
> > top of each other.
> >
> > I would really like something like Visual InterDev 6 where multiple
> > developers connect their own solutions to a web application and all of the
> > resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> > good
> > way to do this with the Report Designer?
> >
> > Any thoughts or links regarding multi-developer coordination issues would
> > be
> > greatly appreciated.
>
>|||If you've made shared data sources you should have an .rds file which
is the actual data source.
Once it's been migrated to the rs server it doesnt' get updated unless
it's deleted or someone explicitly updates it, so for developing local,
we've just emailed the rds to each other. ( you can find it in My
documents/Visual Studio Projects/ProjectName... or if you're using
source safe they should be able to grab a copy from there)|||On Shared Data Sources, there is also a flag in the Properties
Pages/Configuration Properties/General for "OverwriteDataSources" which
defaults to False. Just make sure no one sets that to True or else you WILL
overwrite your data sources in deployments.
"Al" wrote:
> Is it possible for the Report Designer to "download" or otherwise learn about
> the available shared data sources or is the only way to add the *.RDL file to
> the project?
> Thanks!
> PS: Sorry for all the duplicates, I goofed up while trying to sign up for
> passport at the same time as writing my question.
>
> "Bruce L-C [MVP]" wrote:
> > I always use shared data sources. Once a shared data source is deployed it
> > is not overwritten unless you explicitly do so. When you deploy it will try
> > to deploy the data source, see that one is there and just give you a warning
> > that it was not deployed.
> >
> > If you decide on the folder structure and name your projects the same, then
> > you should not have a problem (as long as you don't name the reports the
> > same.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:EF163B30-8423-4F02-8B09-FC9C50D6A070@.microsoft.com...
> > > Two crystal reports developers are about to join me writing reports for
> > > Reporting Services. Neither has used RS before and I don't believe either
> > > has ever used Visual Studio.
> > >
> > > I am concerned about what will happen when they build/deploy a project.
> > > Will we end up with multiple unwanted folders and data sources? I thought
> > > maybe we could all use copies of the same solution but I also want to use
> > > SourceSafe and i dont want to worry about checking different versions in
> > > on
> > > top of each other.
> > >
> > > I would really like something like Visual InterDev 6 where multiple
> > > developers connect their own solutions to a web application and all of the
> > > resources are visible (asp, html, css, js, jpeg, gif, etc..) Is there a
> > > good
> > > way to do this with the Report Designer?
> > >
> > > Any thoughts or links regarding multi-developer coordination issues would
> > > be
> > > greatly appreciated.
> >
> >
> >

Best practices for copying databases...

I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.
Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:

> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.
|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<data base_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com

Best practices for copying databases...

I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:

> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the productio
n
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.b
ak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.com

Best practices for copying databases...

I have two SQL 2K servers, one production and one test. I'd like to know
what the best process is for copying multiple databases from the production
server to the test server.Put the destination database in bulk logged recovery model for one.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Hello,
Backup the database in local machine
If the file is really huge then zip the file (This reduces network traffic
and copy the files in network)
Copy the zip file to destination server
Unzip the file and and Restore the database
Thanks
Hari
"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.|||Online? Offline?
Offline:
Detach Database(s) In Question,
Copy The Files Over,
Attach Database on both servers.
Online:
Hari Prasad Suggestion.
Or
Use Copy Database Wizard, I haven't used these in 2000, in 2005 they have
the SSIS packages that work great ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Fuel451" wrote:
> I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the production
> server to the test server.|||"Fuel451" <Fuel451@.discussions.microsoft.com> wrote in message
news:E6241041-7CF4-4C9C-A6C6-93491836F047@.microsoft.com...
>I have two SQL 2K servers, one production and one test. I'd like to know
> what the best process is for copying multiple databases from the
> production
> server to the test server.
It depends.
We have very high-speed networking between our prod and test servers, so I
usually create an empty test database with the right name and restore the
test test from any convenient backup on the production system directly from
the backup file via the admin share for the disk device. On the test server
a command like:
restore database <database> from
disk='\\<prod_server>\<drive>$\mssql\backups\<database_most_recent_backup>.bak'
with stats=2
usually works like a champ. Your filegroup names might have to be
compatible (we use the defaults). You might have to do user fixups on the
test database - you can build a script for that and execute it after the
restore is complete.
Posted via a free Usenet account from http://www.teranews.comsql

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.
Nick
Search on http://www.sql-server-performance.co...ced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.
|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/pro...n/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.Nick
Search on http://www.sql-server-performance.c...nced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/pr...in/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best practice for managing multiple servers

Hi, Does anyone know of a whitepaper or web site containing best practice
information for the Administration of multiple SQL servers?.
I am looking at Administrating multiple servers and would like to create a
one point for administration and notification of the failure of jobs.
Many thanks.Nick
Search on http://www.sql-server-performance.com/advanced_search.asp
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.|||There may be some useful information here:
Operations Guide -SQL Server 2000
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops0.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:D54FBB07-4BE9-4620-BCC0-5DC79015AB7C@.microsoft.com...
> Hi, Does anyone know of a whitepaper or web site containing best practice
> information for the Administration of multiple SQL servers?.
> I am looking at Administrating multiple servers and would like to create a
> one point for administration and notification of the failure of jobs.
> Many thanks.

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.
> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.
> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/technologies/integration/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Best method to migrate data?

Hello all:
The task: migrate large amounts of data from multiple machines to
corresponding databases on one machine, which will serve as a data
warehouse. The warehouse machine will have no transactions, so I plan
to have logging turned off for it.
I understand that, although tables on the warehouse will be heavily
indexed, that I will want to disable the indices and constraints prior
to the bulk loads.
My question: what is the best methodology to actually move the data?
Should I use DTS packages, or export to files and use BCP, or ?
Also, should I be content to clear out and reload the warehouse tables
to be sure to catch any changes to existing records from the production
database, or is it more feasible from a performance standpoint to
update existing records and only insert new records?
Many thanks,
zdrakec"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147096374.882929.83610@.i39g2000cwa.googlegroups.com...
> Hello all:
> The task: migrate large amounts of data from multiple machines to
> corresponding databases on one machine, which will serve as a data
> warehouse. The warehouse machine will have no transactions, so I plan
> to have logging turned off for it.
You can't turn off logging.

> I understand that, although tables on the warehouse will be heavily
> indexed, that I will want to disable the indices and constraints prior
> to the bulk loads.
> My question: what is the best methodology to actually move the data?
> Should I use DTS packages, or export to files and use BCP, or ?
Use SSIS. No question. It doesn't matter what versions of SQL Server you
are using. SSIS is the right tool and it can load whatever you have.

> Also, should I be content to clear out and reload the warehouse tables
> to be sure to catch any changes to existing records from the production
> database, or is it more feasible from a performance standpoint to
> update existing records and only insert new records?
>
It depends. Consider loading a staging table. Then you can mix and match
INSERT, UPDATE, DELETE to fit your needs.
David|||Hello David:
Thank you for your remarks.
I was under the impression that the database could be started in a
no-logging mode. Am I mistaken, then, in this impression?
Also, I am unfamiliar with SSIS. Can you point me towards information
about it?
Thanks much,
zdrakec|||"zdrakec" <zdrakec@.yahoo.com> wrote in message
news:1147100405.620218.169540@.j33g2000cwa.googlegroups.com...
> Hello David:
> Thank you for your remarks.
> I was under the impression that the database could be started in a
> no-logging mode. Am I mistaken, then, in this impression?
Yes. In the Simple recovery model the log is still written. It's just
truncated occasionally so it doesn't grow.
This doc is 2005, but the recovery models are the same in 2000.
Overview of the Recovery Models
http://msdn2.microsoft.com/en-us/library/ms189275.aspx

> Also, I am unfamiliar with SSIS. Can you point me towards information
> about it?
>
http://www.microsoft.com/sql/techno...on/default.mspx
http://msdn2.microsoft.com/en-us/library/ms141263.aspx
http://www.sqlis.com/
David|||Thank you sir!!

Best design method to allow for "dynamic" records

First, a quick overview of my project. I'm designing a vehicle
tracking system that takes data from multiple types of GPS devices,
stores the data in a common database, and allows the user to view
device locations in real time or create reports on previous activity.
Currently we're only using one type of device, but I'm trying to
futureproof the app so I don't have to redesign it down the road.
Since the devices have different capabilities, I'm trying to come up
with the best method to store their data in a common format. For
example, say I have two devices, DeviceA and DeviceB. Both can report
their latitude, longitude, speed, heading, and a timestamp. DeviceA
can also report an odometer value, whether or not it has a GPS fix,
and telematics data. DeviceB cannot report those values. Along with
this data, every record will be tagged with address information on the
server side. Down the road, we may have DeviceC, DeviceD, etc with
new capabilities.
Now, the question is, how can I store all of this information in a way
that is simple to search/order/etc that is also "dynamic"? I've
thought about using three tables...one would contain "basic" record
info...latitude, longitude, speed, heading, timestamp, and a reason
code for why the record was sent (ignition on/off, start/stop, etc).
A second table would contain address information (street number,
street name, city, state, ZIP) and would be linked back to the basic
table. The third table would contain a single field with an XML
fragment detailing the rest of the information for that record
(odometer value, GPS fix status, telematics, etc).
Problem is, I can't find any method to allow me to search through XML
contained in a column other than a full text search. I also have the
problem of creating a result set containing all of the dynamic columns
(or selected ones only) to be returned to my ASP.NET application for
reporting.
Other methods I have thought about...storing all "extra" info in a
huge table with columns for each value. This would result in a lot of
wasted space (NULL values everywhere for devices that don't support
those features) and new columns would have to be added each time a new
device is supported (if new features are provided). Yet another
method...store values as key/value pairs in a separate table. This
would be rediculously slow though...I would have to generate some
heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
values I need and dump them into a result set.
The data volume will be large (50K-60K records per day) and reporting
needs to be fairly responsive (web based reporting system...generate a
result set from SQL Server, typically using a date/time range, with
the required fields and pass back to data access layer for final
processing). So...out of the three methods I've thought about...any
comments or thoughts about which would be the best way to go? Are
there any other methods I should take into consideration? I know SQL
Server 2005 is supposed to have much improved XML support if I go that
route, but that's not an option at this point...I'm stuck with using
2000 for now.
Thanks for any help you can offer...it will be greatly appreciated!
Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:

> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:

> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Hi Jeff,
SQL 2005 in effect adds the ability to XQuery the data on a column; there is
the "XML" column type that allows this. Also the performance is quite good
since (from what I understand) data is optimized and indexed based on the
XSD information given when defining this field.
Of course this approach is not an option since Yukon is still many months
away.
See below on the poinst I suggest you to follow... and a mid-way solution
that can help!
Ciao,
Adriano

>...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
Yes this kind of normalization is very good because you don't rely on actual
fields to store information, thus reducing space wasting.
Performance-speacking: SQL Server 2000 has a great set of features you can
use to improve querying speed.
1) Indexed views: You can perform aggregations on this table using Indexed
Views in order to have real-time view of your data in a "de-normalized" and
summarized way, where necessary.
2) Mantain only last-month data here so you have last-month queries quite
fast; move the oldest ones in a parallel "history "table. Report this table
only when explicitly requested by the user.

> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
Another solution?
Build many tables, one for each "device type".. .where you can store "extra"
information without wasting space.

> Thanks for any help you can offer...it will be greatly appreciated!

Best configuration for Analysis Services

I am looking for opinions on what server configuration is best for Analysis
Services 2000.
Should we emphasize CPU speed/multiple CPUS, RAM?
We have a SAN attached via a Gigabit LAN.
Thanks.See if this helps
http://www.microsoft.com/technet/tr...ze/ANSvcsPG.asp
There is a section on hardware resources, as Tom Chester has quoted multiple
places, Analysis Services is very heavy on RAM and SQL2K AS can only use
3gb.
Ray Higdon MCSE, MCDBA, CCNA
--
"Bruce Lester" <bruce_lester@.email.com> wrote in message
news:wDy_b.391402$na.741449@.attbi_s04...
> I am looking for opinions on what server configuration is best for
Analysis
> Services 2000.
> Should we emphasize CPU speed/multiple CPUS, RAM?
> We have a SAN attached via a Gigabit LAN.
> Thanks.
>

Best approach with DTS

Let me see if I can explain this.

I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.

Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.

Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.

Step 3
The returned rows here, need to be limited to key values returned from step 2

Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.

What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.

I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.

What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.

I'm open to ideas for alternatives

best approach for "trigger on commit"?

(I've searched the forum but haven't found anything that seems to address my specific question)

We have multiple tables that get updated as part of a transaction.

After all the data is added succesfully there is processing that needs

to be done, so we'd like to use a trigger to kick it off automatically. The schema can be thought of as including a "master report" table that gets updated once whenever a new report is submitted and multiple related tables each corresponding to different report sections. The PK-FK relations imply that the master report will be updated prior to the subsidiary sections, but the exact order of updates to the subtables is not defined.

I thought of attaching a trigger to the master report table but I can't find an approach or mechanism that would allow us to defer the triggered code until after all the tables have been updated.

It almost seems like I want a transaction trigger. Something that would be executed before or after a COMMIT TRAN. But the

post-processing code does not have to be deferred until after the

transaction is committed, it just mustn't be started until all the

tables participating in the transaction have been updated, otherwise

all the data needed won't be present.

There are several work-arounds that come to mind, but none are as

elegant as having a TRIGGER ON COMMIT capability. I'd be interested in

how other people have solved this problem.

Here's some of the work-arounds we came up with

1. Identify the last table to be updated, and place a trigger on that. Ugly for obvious reasons and very prone to breaking.

2. Create an "update history" table with a trigger on it. After the

actual transaction commits, a row should be added to this table by the

db user. Less ugly, but still not pretty.

3. Variation on #2 -- after an update transaction, have the database user call a stored procedure. Just as ugly as #2

4. Create a view that is used to write through to the underlaying tables, and have the trigger on the view. Unfortunately the large amount of data (approx 20 tables participate in one update transaction) makes this very unattractive. (Imagine an INSERT statement with 300 values!?)

Thanks in advance for suggestions and help

TerryPesonally, I would recommend any solution that does not use Triggers. Triggers are evil. They are a maintenance nightmare. After you finish your "transaction", you can call a stored proc that takes care of the rest of the updates which is a more controlled way of doing than doing it in a trigger. Do a simple google search on issues with triggers and you will be convinced enough to not use them.|||

We, I cannot agree with your feelings on triggers (Cursors are evil, not triggers. Triggers are just a bit difficult :) I do agree that this is not the place for a big trigger (considering how SQL Server triggers work.)

An alternative that I might suggest if this can be done asynchronously is that you have a job that queries your tables to see when the transaction is finished (either you check for the existance of data in all tables, or you write some sort of control row (possibly using triggers here) when you do each insert into the other tables. Then the process can check to see when all of the data for a "transaction" is completed and do your processing.

|||I can second Louis, triggers always had and will have a bad reputation because of the wrong implementation of users. Sure, in some cases they behave slow, they act syncronously (which can real nightmare if not considered) and are fired per statement not per row, so a bit implementation afford is needed here, but triggers can help your through the day for enforcing business rules which can′t be accomplished through any middle tier (if the database is directly access by several applications). So I would say that they are as evli as you force them to be :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||After the database is developed it may have data added by an unknown number of subcontractors. So it is important that the "api" be as simple as possible and that the database be as intelligent as possible. Building business logic into the database in the form of SP and triggers has clear advantages for us over an approach that depends on the goodwill of users to follow our suggested/required calling structure.

In other words, having a document that says "after inserting all of a report call SP after_insert_processing()" opens up the door for someone to forget to call the SP. Having a mechanism in place that detects the operation and automatically calls the SP is much more attractive to us.

Hmmm.....|||BTW thanks for all of the suggestions. We may go with a job that checks the time of the last update and determines from that if the post-insert processing should be done.|||

You can do something like below:

1. Create a tracking table

2. Insert a row from the tracking table for each operation on the other table(s) (Need not be at insert level. You can determine the granularity)

3. Have the insert trigger on the tracking table determine the matching conditions to fire the SP. This can be done by checking for count of specific number of operations per report for example. The post_insert_processing SP can be fired using an on-demand SQLAgent job so that it is asynchronous.

You could do away with the tracking table approach and simply do the check in every SP that inserts into the other table(s) also. Either way you can centralize the logic to check in a SP (by using simple queries against the required table(s)).

|||

My first question would be..."How do you currently ensure all information is updated correctly?"
My second question would be..."Is this a manual process that you want to include in your automation?"

It sounds as if there is some form of linear progression with a definitive end but you want to avoid this assumption for fear of change.

So...to satify your anxieties, create an independant action (the approach is insignificant).

Forget about the clever trickery or sly workaround, code a 101 solution to a 101 problem.

Adamus

best approach "wher in" or JOIN

Hi,

I have 2 sets
Set1 has few UNIQUE items say workitem Ids 2,3,4

Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8

I need to look for Set1 in Set2 so i get 2,3,4 etc...

which is the best way

Set2 Left join Set1

OR

Select item from Set2 where item in (Set1)INNER join, not LEFT OUTER|||I posted your answer over at SQLTeam|||see if this helps

books online {Using EXISTS and NOT EXISTS to Find Intersection and Difference} QUOTED IN ENTIRETY
Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.

The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

Here is the result set:

city
---
Berkeley

(1 row(s) affected)

Of course, this query could be written as a simple join.

USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

This query could also be written as:

USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)

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.

behavior of ssis packages running under SSMS manually vs. job agent automatically

I have a ssis package that has multiple large lookups without memory restriction. When running the package manually from SSMS on the same server it runs on when running automatically under the job agent, the package errors out when the server memory gets depleted by the loading of the large lookup reference data. One of the messages I get is

"An out-of-memory condition prevented the creation of the buffer object. "

Anyway, the package runs successfully when it runs automatically under the job agent.

I was curious as to why the above happens. Is that a bug or is the run time behavior different under these 2 environments by design.

js40

If you are running through SSMS, you are likley executing the package via your machine, not the server. Do you have the full SSIS client installed? (Do you have a SQL Server Integration Service service when looking at "Services" under the control panel?)|||

No, i ran the package on the same server as where it runs automatically under sql agent. I started it manually from SMSS under remote desktop. The server is sql server 2005 (64 bit) with SP1 and 8gig memory.

Actually, several times it crashed SMSS and the last thing I saw just before the SMSS window just plain disappeared was some errors related to memory.

Below are different errors i got when i ran this package serveral times.

1) The Data Flow task failed to create a required thread and cannot begin running. The usually occurs when there is an out-of-memory state.

2)The system reports 99 percent memory load. There are 8588873728 bytes of physical memory with 67133440 bytes free. There are 2147352576 bytes of virtual memory with 27369472 bytes free. The paging file has 12954959872 bytes with 4205600768 bytes free.

3)A buffer failed while allocating 2048 bytes.

4)An out-of-memory condition prevented the creation of the buffer object.

js40