Applying SP4 on Replicated databases

Hi,
We have transactional replication with 30 subscribers. We are planning to
upgrade to sp4 from sp3a. Publisher and distributor are in the same server.
So is there any order(sequence) like first we have to upgrade publisher and
then subscriber...
Is there any known issues/impact on publisher or subscriber.
Thanks in advance for your help.
Thanks
Prabhu
The rule is
Local Distributor - upgrade the publisher/distributor first then the
subscribers
Remote Distributor - upgrade the distributor, then the publisher, and
finally the subscriber(s).
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:B7770777-C2B2-4E30-9F13-354A18A3666E@.microsoft.com...
> Hi,
> We have transactional replication with 30 subscribers. We are planning to
> upgrade to sp4 from sp3a. Publisher and distributor are in the same
> server.
> So is there any order(sequence) like first we have to upgrade publisher
> and
> then subscriber...
> Is there any known issues/impact on publisher or subscriber.
> Thanks in advance for your help.
> Thanks
> Prabhu
>
|||Thanks cotter,
So we don't need to worry about the subscriber type, whether its read-only
or updatable subscriber. Am i right ? or is there any difference for these
types.
Thanks
Prabhu
"Hilary Cotter" wrote:

> The rule is
> Local Distributor - upgrade the publisher/distributor first then the
> subscribers
> Remote Distributor - upgrade the distributor, then the publisher, and
> finally the subscriber(s).
> "Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
> news:B7770777-C2B2-4E30-9F13-354A18A3666E@.microsoft.com...
>
>
|||Yes you don't have to worry about the subscribers. Just do them last.
I always shut down the SQL Server agents on the boxes before doing the
upgrades. You will need to notify your users that you are doing upgrades so
you can kick them off.
Hilary
"Prabhu" <Prabhu@.discussions.microsoft.com> wrote in message
news:EC578700-3B73-4C38-9255-4FA97C79A7B4@.microsoft.com...[vbcol=seagreen]
> Thanks cotter,
> So we don't need to worry about the subscriber type, whether its
> read-only
> or updatable subscriber. Am i right ? or is there any difference for these
> types.
> Thanks
> Prabhu
>
>
> "Hilary Cotter" wrote:

Applying SP on SQL Cluster (active/passive) after new nodes Added

Ran into a problem in scheduling applying the Service Pack to our SQL
2000 active/passive cluster when we add new nodes. 50 plus databases and an
unknown number of apps.
I'm wondering what people do to ensure that no apps/people are trying to
access the databases when the service pack is applied.
Our application staff says there is no way they can guarantee that an
app or person will not try to access one of the databases while the service
pack is being installed.
My plan is to evict one of the existing nodes then add the two new nodes
one at a time and set up the heartbeat. Then install SQL 2000; then apply
SP3a. Yes I know SP4 is available; however, one of the apps will not run
with SP4.
The service pack brings the database engine up in single-user mode during
the SP install. Unless your applications are connecting as SA or
equivalent, there should be no conflict.
If the apps are connecting as SA, change the password before applying the SP
and change it back when you are done. Then fix that glaring security and
managability hole.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:FA6E93F9-78FC-497E-A4F0-E4D459F85CA9@.microsoft.com...
> Ran into a problem in scheduling applying the Service Pack to our SQL
> 2000 active/passive cluster when we add new nodes. 50 plus databases and
> an
> unknown number of apps.
> I'm wondering what people do to ensure that no apps/people are trying
> to
> access the databases when the service pack is applied.
> Our application staff says there is no way they can guarantee that an
> app or person will not try to access one of the databases while the
> service
> pack is being installed.
> My plan is to evict one of the existing nodes then add the two new
> nodes
> one at a time and set up the heartbeat. Then install SQL 2000; then
> apply
> SP3a. Yes I know SP4 is available; however, one of the apps will not run
> with SP4.
|||I thought in single-user mode, that any ID could be used and the 1st one
wins. SA or not. I also thought that the service pack brings the database
engine up and down several times while it is applying the service pack.
The other restriction (db_owner, sysadmin, bd_creator) isn't an option for
us either since many of the app IDs are db_owner in their database.
"Geoff N. Hiten" wrote:

> The service pack brings the database engine up in single-user mode during
> the SP install. Unless your applications are connecting as SA or
> equivalent, there should be no conflict.
>
> If the apps are connecting as SA, change the password before applying the SP
> and change it back when you are done. Then fix that glaring security and
> managability hole.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
> news:FA6E93F9-78FC-497E-A4F0-E4D459F85CA9@.microsoft.com...
>
|||There is single-user mode for a database, and then there is single-user mode
for the entire database service. The SP starts the entire service in
single-user mode and immediately claims the connection.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:98107803-0953-4B12-986E-FB82CDCBA45B@.microsoft.com...[vbcol=seagreen]
>I thought in single-user mode, that any ID could be used and the 1st one
> wins. SA or not. I also thought that the service pack brings the
> database
> engine up and down several times while it is applying the service pack.
> The other restriction (db_owner, sysadmin, bd_creator) isn't an option for
> us either since many of the app IDs are db_owner in their database.
> "Geoff N. Hiten" wrote:
|||Thanks. So do I even need to get all the apps down before I start applying
a service pack? I would think so, so they close out of what they are doing
in an orderly fashion. Just not worry about them trying to get in while the
service pack is running.
"Geoff N. Hiten" wrote:

> There is single-user mode for a database, and then there is single-user mode
> for the entire database service. The SP starts the entire service in
> single-user mode and immediately claims the connection.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
> news:98107803-0953-4B12-986E-FB82CDCBA45B@.microsoft.com...
>
|||You need a standard practice for maintenance, regardless of whether you are
applying a hotfix or an internal update. This includes procedures for
stopping and starting the entire system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Pat Hall" <PatHall@.discussions.microsoft.com> wrote in message
news:D497CBD2-637D-490C-9BAD-6AA49F145000@.microsoft.com...[vbcol=seagreen]
> Thanks. So do I even need to get all the apps down before I start
> applying
> a service pack? I would think so, so they close out of what they are
> doing
> in an orderly fashion. Just not worry about them trying to get in while
> the
> service pack is running.
> "Geoff N. Hiten" wrote:

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.
I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:

> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.
|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:

> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new serve
r
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the curren
t
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

Applying service packs

Hi,
I have a production server running SQL 2000 SP2 on Windows 2000 server. I
have to migrate all the databases on this production server to a new server
running SQL Server 2000 SP4 on Windows 2003 Server.
Are there any known issues? How should I proceed ? Apply SP4 on the current
Production Server first and then migrate? Any insight will be greatly
appreciated. Thanks.I would recommend applying SP4 to the current server before the migration.
This way all your system databases will match between systems.
If you are going to copy the system databases as well (which I would
recommend if you can keep all the directory paths the same) you will need to
do a sp_droplogin and sp_addlogin to change the server's name to the new
server name.
There should be no issues with this migration.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"sharman" wrote:
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.|||Hello,
Best option:-
1. Install SP4 to current production system
2. Copy the databases to new server.
3. You can also copy the system databases. This will ahelp you not to create
any jobs, DTS, Logins...
Thanks
Hari
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F892EF13-069B-4E8A-9DB7-0EF942C90450@.microsoft.com...
> Hi,
> I have a production server running SQL 2000 SP2 on Windows 2000 server. I
> have to migrate all the databases on this production server to a new
> server
> running SQL Server 2000 SP4 on Windows 2003 Server.
> Are there any known issues? How should I proceed ? Apply SP4 on the
> current
> Production Server first and then migrate? Any insight will be greatly
> appreciated. Thanks.

Apply folder structure in SQL Server 2005

I am in the midst of the process of migrating mdb databases to SQL Server.

Since I have many databases, I want to organize them in a structured tree order, i.e. in folders and subfolders, like the example of "System Databases" folder.

Is it possible? and if the answer is yes, then how?

Any database can be moved to any location which is permitted to the domain account MSSQL is running under . Maybe it's tricky a little for the system databases master and tempdb; anyway all the information can be found on MSDN or BOL. To move user databases see sp_detach_db/sp_attach_db and http://search.msdn.microsoft.com/search/results.aspx?view=msdn&qu=moving+master+database for system db's.|||

I presume you mean you want to group them into folders in the GUI. Antoher poster assumed you meant you wanted to move the MDF/LDF files on the file system.

If you're talking about the GUI, the answer is "no" today.

This is not really a work-around, but FYI - it is possible to add extended properties to databases and then write a TSQL query that groups and sorts by the extended property value. In other words, it is possible to define your own database-level attributes and then sort and filter on them.

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert Lie
There's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie
|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 = SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application Databases storing location

We have SQL Database in Cluster volume F:
Cluster volume is mounted in the primary server A1.
We have production and test databases of the applications
stored in the Cluster Storage F:.
Is it possible to store all the test databases of the
applications in the Clocal harddisk) of the primary
server?
If there is a solution pls advice.
You can copy data to anywhere you would like. To use the data with
clustering, you have to have the ability for that data to be reached by any
clustered node, even after a failure. If you keep anything local, and that
machine
fails, other nodes will not be able to access it.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||A clustered SQL server must store data on a drive that is in the same
resource group. The SQL server must be dependant on the physical disk(s)
that clustered data is stored on.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:743201c430db$f3018270$a401280a@.phx.gbl...
> We have SQL Database in Cluster volume F:
> Cluster volume is mounted in the primary server A1.
> We have production and test databases of the applications
> stored in the Cluster Storage F:.
> Is it possible to store all the test databases of the
> applications in the Clocal harddisk) of the primary
> server?
> If there is a solution pls advice.
>
|||Can the Clocal harddisk) of the primary server be
included as part of the same resource group as the
clustered SQL server?

>--Original Message--
>A clustered SQL server must store data on a drive that is
in the same
>resource group. The SQL server must be dependant on the
physical disk(s)
>that clustered data is stored on.
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||Fine. We do not want the test data in the cluster mode and
need not be available in case of failure.
Is it possible to have SQL server in Cluster and
applications data in the local?

>--Original Message--
>You can copy data to anywhere you would like. To use the
data with
>clustering, you have to have the ability for that data to
be reached by any
>clustered node, even after a failure. If you keep
anything local, and that
>machine
>fails, other nodes will not be able to access it.
>Cheers,
>Rod
>"Anonymous" <anonymous@.discussions.microsoft.com> wrote
in message[vbcol=seagreen]
>news:743201c430db$f3018270$a401280a@.phx.gbl...
applications
>
>.
>
|||No. C: is not a shared cluster resource.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886301c4326d$56ac1a00$a301280a@.phx.gbl...[vbcol=seagreen]
> Can the Clocal harddisk) of the primary server be
> included as part of the same resource group as the
> clustered SQL server?
> in the same
> physical disk(s)
> in message
> applications
|||No, sorry you can't do that. All SQL data needs to be on the shared storage.
Cheers,
Rod
"Anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:886b01c4326e$406dde30$a301280a@.phx.gbl...[vbcol=seagreen]
> Fine. We do not want the test data in the cluster mode and
> need not be available in case of failure.
> Is it possible to have SQL server in Cluster and
> applications data in the local?
> data with
> be reached by any
> anything local, and that
> in message
> applications

Application and database performance benchmarks

Hi
I need to develop and implement procedures to monitor performance of sql
server 2000/2005 databases and various applications. Is there a place where I
can look at for creating benchmarks .
Thanks
--
ontario, canadaChapter 1 of Inside Microsoft SQL Server 2004 Query Tuning and
Optimization has a section on creating a baseline for your workload,
and then monitoring the workload. That would be a reasonable place to
start to study the subject.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Apr 2008 10:10:01 -0700, db <db@.discussions.microsoft.com>
wrote:
>Hi
>I need to develop and implement procedures to monitor performance of sql
>server 2000/2005 databases and various applications. Is there a place where I
>can look at for creating benchmarks .
>Thanks

appending data from database 1 to database 2 (live into Dev env)

have 2 databases in sql server 2000

Live
Dev

I need to append all the data from LIVE into DEV environment. I have tried using MS Access (linking tables & importing tables and running APPEND query to update the rows from LIVE to DEV but PK & FK is causing problems as some data will have the same ID's...

Could I use DTS--Access wizard in SQL Server.

What is the best option to use?

Thanks all in advanceIf this is a one-time transfer, or if does not need to occur on a scheduled basis, you can just right click on your production database in Enterprise Manager and follow the wizard to export data.|||The best thing, IMHO, is to restore you production to development. It will force you to write a script to do a delta, and it will clean up your dev environment. If you don't have the space, buy it. It is cheap.

Append Views

My Problem is

I have 2 views --> 2 Databases (2 Products) but there are same fields (Same structure)

and I have to created the report by Crystal Reports to compare the Quatity of all product in my Company

So how Can I combine them (2 views with the same recoed but not the same data)

thanks for helping me

Kate

You can try Linked Servers if your databases are on different servers or on different instances.

If they are on the same instance then you can use

select * from DatabaseName.dbo.TableName
OR
select * from DatabaseName..TableName

Eralper

Append Data

Hi, i need some help as i'm not good with SQL. I have 2 databases n SQL and i'm trying to write a rountine were it inserts all the new data from a live table to another table in another database.

Is there any way how to do this?

Once i have figurered that bit out i need to run the rountine every night automaically.

I would really appreciate if you have the answers for my questions

Thankyou in advanceinsert
into anotherdb.anothertable
select *
from livetable
where data = 'new'|||insert
into anotherdb.anothertable
select *
from livetable
where data = 'new'

You funny

What the DDL look like?|||We've had a topic name change - ANSI SQL - Yay! :D|||You funnyi are oney tryna be hepful :angel:

i used all the available information in the oiriginal post!!|||R937: Is the "anotherdb" from your example code a user (as in Microsoft/Sybase), a schema (as in Oracle/DB2), a database (as in MySQL/Pervasive), or something different? I guess that my point is that your code as presented will do slightly different things, depending on which database implementation is used to execute it.

-PatP|||PatP: yes :)|||Rudy's query works in DB2 even if both tables reside in different databases (not only different schemas) - DB2 for z/OS, that is. ;-)

The interesting thing is that ISO/IEC 9075:2003-2 is very, very vague about what a "database" actually is. This results from the fact that different DBMS vendors apply this term to different concepts (and DBMS vendors drive the standard).

@.Hemish: you should be a bit more specific what you mean. Do you refer to schemas/schemata or federation?|||Instead of writing a query you might be better using the backup tools available for your particular database system. For MSSQL you can set up nightly backup jobs using the scheduling tool that is available. In MySQL you can use the Mysqldump utility (from cmd line) to run a backup, which you could schedule using a cronjob (if unix) or windows scheduler (if win).|||Backup tools are not standard SQL, of course...|||Backup tools are not standard SQL, of course...Yeah, Standard SQL doesn't support backups (literally true, since backups are only supported through vendor extensions, the standard definitions don't provide any references to backups).

-PatP

APP_DATA directory

If I already have SQL2005 installed, can I create SQL express databases for distribution in my app, or do I need to install SQLExpress to run side-by-side?

I actually had SQLExpress originally but upsized it to the full version. Now I want to be able to create portable DBs with my application.

And if this is possible, how do I go about creating the DB?

Thanks!

You can refer to the upgraded SQL2005 instance just as you did to SQL Express. One thing to note is that if you have set "User Instance" attribute to true in your connection string, the connection to SQL2005 may fail with error message saying "User Instance can only be used with SQL Express..." (not exact, but something like this)|||

Thanks for the answer.. that helps on that error that I have received...

I think I might have been a bit unclear though...

On my local dev machine, I have the full SQL2005 version, but I want to create an application that can be distributed with a SQLExpress database in its app_data directory. This application will not require that the user attaches the .mdf through sql2005, but only that they have SQLexpress running as the DB will live locally in the APP_DATA directory.

Question is: Can I create the DB through my version and then just drop the .mdf in the APP_DATA directory and will it be compatible with SQLExpress?

Also, can I use a SQLExpress connection string / DB on my system during development without having to attach the DB to my sqlserver instance?

Sorry if my questions seem ignorant, but I am just trying to wrap my hands around the whole thing.

Thanks!

|||

NevermindIf you do not want to attache the database file at run time, you have to use a database in your SQL Server. That's because what your application needs is not only a database file, but also needs a SQL Server instance. So if you want to switch between SQL Express and SQL 2005 (means different SQL instances) without attaching the database file at run time, you have to change your connection to SQL, and move database as well. There are some options you can choose to move database:

How to move database using detach/attach:

http://msdn2.microsoft.com/en-us/library/ms187858(d=ide).aspx

And copy database with backup/restore:

http://msdn2.microsoft.com/en-us/library/ms190436(d=ide).aspx

This article shows a good torturial for changing SQL connections in web application:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Anyways, I recommend attaching the database file at run time--then you just need to change connection to SQL without moving database

app.config and connectionstrings with several databases

Hello all

I need to know the best way to work with databases (Sql Server) in my app.config.

I have an VB application that binds data from a SQL server (All Customers install these application and retrieve data via VPN). I use two databases for each customer. The first DB has some commun tables/data and the second one is its ID (Until now I setup manually the connections because we don't have more that 10 Customers but we need improve that for next year). I know that a connection string is created in the app.config file .

I don't know if it is possible from this way or if there is another way to do it... but the goal is:

1. Customer is logged in the application -User Name, ID, Password

2. Customer retrieve a database with commun data and his own database (that is his ID). (From the app.config?) How can I setup my app.config in order that every customer retrieve his database automatically when it is logged?

Thaks, I don't know if it's clear but I didn't find another way to explain my trouble

Hi

I hope I am following you.

If there are many customers I don't think it is a good way to store all there personal info(their ids) in app.config.

You can put all info into database and retrive them when the customer is logged in.

I don't what do you mean by saying own database , you might need to better design your database since it is not a good idea to add a new database(or even a new table) when you create a new customer.

Apostrophe Usage

Ok, I still have some uncertainty as to just exactly how this whole apostrophe thing works with databases. I understand that it is a reserved character and so when a sql query runs into one of these creatures it looks at it as something other than a normal character.

I am working primarily in vb/asp/sql server with a little bit of access. I am familiar with the instrinsic 'Replace' function and I use it but I still have occassional problems.

I would like any information I can get on just exactly why/how this thing works and how to work-around the apostrophe when writing to, reading from, and validating data from sql server/access/any databases.

Thanks!why/how it works: it is designated as the string delimiter

work-around: code two of them in a row inside a string to get one

example: insert into people (surname) values ( 'O''Toole' )

Anyone use Copy Database Wizard across domains?

Has anyone been able to successfully use the database copy wizard to copy databases between domains?
I've got a SQL 7.0 database on domain A. I need to move it to domain B and upgrade it to SQL 2000.
Thanks
SharonDid you try to add the remote server from client network utility by IP Address and correct Port Number. Since it's in different domain, you may require SQL authentication(sysadmin userid/pwd). Make sure you do enought testing before proceeding to production.|||The problem really has to do with the requirement that the user have local admin rights.

I can get the copy wizard to begin using sql authentication, but I always get an error trying to create the OMWWIZE share.

I was hoping for a way around this.