Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny
1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny
|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database. During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views, functions
> and store procedures. When the developer generates the scripts, she needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
2012年3月11日星期日
Any SQL Server Guru?
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
2012年3月8日星期四
Any SQL Server Guru?
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging databas
e
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home
,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wip
e
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and Reporti
ng
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database. Duri
ng
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views, functio
ns
> and store procedures. When the developer generates the scripts, she need
s
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging databas
e
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home
,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wip
e
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and Reporti
ng
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database. Duri
ng
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views, functio
ns
> and store procedures. When the developer generates the scripts, she need
s
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>
2012年2月13日星期一
AntiVirus Software for SQL Cluster Servers
Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
订阅：
博文 (Atom)