Archiving logging db

I'm fairly new to SQL. I've inherited a logging db that records the actions taken on a web service. One of the first questions I'm being asked to solve is to explain how I should concatenate the db and save older entries to a archived db.

I'm sure this is a pretty simple action - just identify the date I want to archive from and develop a query that copies all entries from before that time period to a separate db. I'm looking for some guidlines for this kind of action, but there's so much info on SQL out there, I'm having trouble parsing out the noise and finding the answers I'm looking for. Can anyone point me in the right direction, or give me advice?

Thanks

Levi

One way to do this is to create an SSIS package:

http://msdn2.microsoft.com/en-us/library/ms169917.aspx

|||

Create a archiving table (Arch_Table) in the same db or different db with same schema.

insert into dbname..Arch_Table

select * from Table where dtColumn <= dateadd(mm, -3, getdate()) -- two archive 3months and older data

while 1 = 1

begin

set rowcount 100 delete 100 rows at a time... if you are using sql 2005 you can use top clause check bol for details

delete from Table where dtColumn <= (select max(dtColumn ) from dbname..Arch_Table )

If @.@.rowcount = 0

Break

end

sql

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe|||

I'm a NS newbie, creating my first NS app which indeed does notifications based on orders with line items (parent/child records).

However, I don't feel architecturally, pulling outside data (line items) is appropriate for either the content formatter or the delivery channel. IMHO, they should be responsible for (and only for) content formatting and delivery, respectively.

My intended solution is to flatten the order details much earlier in the process - in my custom event provider (which is a component responsible for data!). I need a custom event provider for other reasons (data coming from a web service), but this approach to handling parent/child records shouldn't require a cutom provider - I presume it'd work (even easier) with the SQL provider though I admit I haven't used that provider.

The trick is simple: I define the event class (and notification class) to contain the fields for the parent Order, and my order details (order line items) go into a single xml type column of this event class.

Simplified example snippet:

<EventClass>

<EventClassName>OrderData</EventClassName>

<Schema>

<Field>

<FieldName>CustomerName</FieldName>

<FieldType>nvarchar(128)</FieldType>

</Field>

<Field>

<FieldName>OrderDetails</FieldName>

<FieldType>xml</FieldType>

</Field>

Fill in OrderDetails with the aggregate line items and voila, a single "flat" event record with all the data needed for the notification. If all your source data is available in SQL, I imagine you could also do this flattening in the subscription action - pulling order line items out (as XML) based on the parent order ID and pushing them into the notifications view?

Note how this allows me to continue using the built-in XSLT formatter because the entire stream of event data is still just XML when it hits the XSLT. For line items that just an xsl for-each instruction loop to format the line items from the OrderDetails.

Note: to use this technique you need to turn off escaping in your ADF file so that XML flows through to the XSLT file during formatting:

<ContentFormatter>

<ClassName>XsltFormatter</ClassName>

<Arguments>

<Argument>

<Name>XsltBaseDirectoryPath</Name>

<Value>C:\SQL Notification Services\Orders\</Value>

</Argument>

<Argument>

<Name>XsltFileName</Name>

<Value>OrderSubmitted.xslt</Value>

</Argument>

<!-- we disable escaping so that our XML column (orderdetails) stays as

XML when being passed to the XSLT file for processing. The default is to

escape the embedded XML (e.g. "bar&gt;a bar&lt;/bar&gt;&lt" for "<bar>a bar</bar>" -->

<Argument>

<Name>DisableEscaping</Name>

<Value>true</Value>

</Argument>

</Arguments>

</ContentFormatter>

sql

Architecture question

I am a newbie to Notification service and I need your help to determine what's the best way to handle my situation. First of all, I need to determine if Notification service is the right approach.

We have a ASP.NET application that creates purchase orders to various suppliers. Few suppliers are fine with just "Email notifications". But few of them need the PO XML sent to their FTP site. We might have future suppliers and they may want a different mode of communication.

Here's my solution to this problem and I need your expert advice

Step 1: Create an event schema that captures all the item details in a PO.

Question: What is the best way to implement hierarchies [PO Header, PO Line item]? I am thinking of adding all the head fields to the schema for every line item.

Step 2: My delivery channels will be "Email" or "FTP" [custom delivery channel]. Some suppliers will subscibe using email channel, and some FTP

Question: Is there a way I can use the "File" channel to download a PO [with unique file names] and develop an external program to just FTP the file to the supplier?

Step 3: When a new PO is created, I will call my event provider to submit the event [I might use the out of the box Stored procedures]. Depending upon the Subscriber's delivery channel [protocol], the appropriate delivery channel will be chosen.

Any thoughts/help or suggesstions?

I think the easiest hierarchy is a flat structure (one root node, one level of child elements).

As for the File channel, you cannot use the built-in File delivery protocol in this way. It writes results to a single file, and is primarily intended for testing. You could write a custom File delivery protocol that produces unique file names and performs other processing.

|||As Diane mentioned, SSNS really thinks in terms of notifying people of somewhat flat data structures. If you need to have parent-child detail information, what I typically do is create a custom content formatter. In there I make a connection to the database that stores the additional information and query the supplemental information. I then format it along with the normal SSNS notification information (parent stuff) and return it to the distributor.

To write to separate files for each notification, you'll need to create a custom delivery protocol. You can base the file name off the subscriber and perhaps a datetime to make sure it's unique.

If you're parent-detail information is in a database, it's probably easiest to use the SQL Server Event Provider, but you can use the SSNS stored procedures to submit the event if you'd rather.

HTH...

Joe|||

Thanks for your replies.

Joe,

How about if I just pass the "PO" number to the "Custom Delivery Channel" that queries the database for a given PO and then, creates an XML file? Do you think that will make it easier?

Regards

|||Hi Ragas -

That's typically the tact that I take.

In your example (and I'm assuming a bit here since I don't know the details), I'd probably have the event provider submit a few vital pieces of info to SSNS; at a minimum the PO number, but if you have other pieces of information readily accessible then I'd submit that too (especially if it'll save one or more db calls from within the custom component). In your case, you may also want to submit the CustomerId, too.

I typically gather the other information in the content formatter rather than the delivery channel.

HTH...

Joe

Architectural (broker) place of SQL Service Broker

Hi,

I am struggling with the position SSB could take in an SOA. If I would want a broker in the general sense, meaning an intermediary sitting between applications which exchange information through messaging, would SSB be a good candidate? I know Biztalk is probably the primary candidate, but in my scenario I would end up with Biztalk apps with empty orchestrations. Also, I think Biztalk is more expensive to manage. So I am looking for a lightweight broker for a simple SOA targeted at application interoperability, no fancy business processes in sight.

I look forward to some responses.

Kind regards,

Neeva

It would be a good candidate if a lot of your processing is going to be taking place in the database (because SB resides in the db). You can write apps that connect to the db and handle SB messages...but they still have to connect to the db. If you don't plan on that, and want to stay away from BizTalk, you can always write an app that uses Messages Queues (the kind that you configure on your server). Does that help?
Tim|||

Hi Tim,

I understand what you say. The problem is that I don't know upfront where processing will take place. The goal is to have a message broker which acts as an intermediary between applications that exchange messages. I do not want to build point to point connections between these applications. My guess is that I then would have to build an intermediary application on top of SSB which would handle all the messaging via an 'SSB-enabled' database. But then I must be capable of calling other applications (possibly through webservices) from SSB, and return the response via the intermediary application to the calling application. This all sounds rather complex and that, I think, is mostly a sign that it is not the best solution. Also, the mix of webservices and SSB messages doesn't feel right.

I would certainly appreciate your comments!

Kind regards,

Neeva

applying transaction logs from a crashed server

I would like to know the process to rebuilt a new server if the current
server crashes. I can install the server with the same service pack level,
then restore the system databases and then the User databases from the
backups on the new server.
How do we bring in and apply the Transaction logs from the crashed server to
the new server?You mean "the last" log backup? I.e., the log records produced since you produced your most recent
log backup?
It depends on how the server crashed. If the SQL Server is still available, then you just do
BACKUP LOG crashedDb TO .. WITH NO_TRUNCATE
If that SQL Server isn't accessible, then you do the following:
On a working server, create a new database.
Stop that SQL Server.
Delete the database files.
Copy the ldf file from the crashed server in place of the ldf file which you deleted in above step.
Start this SQL Server
BACKUP LOG dbname to ... WITH NO_TRUNCATE
You now have a chain of log backups up until the crash which you can use for your restore. Of
course, all this assumes that you do log backups in the first place and that you can access the ldf
file for your crashed database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F4187089-40A8-420B-B912-77F1B5A79834@.microsoft.com...
>I would like to know the process to rebuilt a new server if the current
> server crashes. I can install the server with the same service pack level,
> then restore the system databases and then the User databases from the
> backups on the new server.
> How do we bring in and apply the Transaction logs from the crashed server to
> the new server?

Applying Stylesheet in Reports (e.g. Font, Header)

I'm using Reporting Service with Service Pack3 and I want to Apply my own stylesheet in reports. I want to give my own style class to Fonts, Headers etc. Can anyone help me ? Is it possible or not ?SQL Server 2000 Reporting Services has somewhat limited style template
support. Improving support in this area is being looked at for inclusion in
a future release.
Until then you have the following options:
1. You can approximate this behavior by creating the specific report you
want and then placing it in C:\ProgramFiles\Microsoft SQL
Server\80\Tools\Report Designer\ProjectItems\ReportProject directory. These
reports "templates" will show up in Report Designer's Add New Item dialog
along with the Report Wizard, Report, and Data Source templates when
you add a new report to a project.
2. For simple wizard-created reports, you can control the style via
modifying the wizard style templates (there's an XML config file for this
documented in the help)
3. If you're wanting to modify the style dynamically after the reports are
created, you could store style information in your database and then query
for that information in your report, setting style properties based on the
query results (e.g. FontFamily
=First(Fields!DetailRowFontFamily.Value,"StyleDataSet"))
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gaurav Shah" <Gaurav Shah@.discussions.microsoft.com> wrote in message
news:ACC0AD53-1693-46DA-B929-9DA55A51D373@.microsoft.com...
> I'm using Reporting Service with Service Pack3 and I want to Apply my own
stylesheet in reports. I want to give my own style class to Fonts, Headers
etc. Can anyone help me ? Is it possible or not ?

Applying SP4. Do you have to provide sa password?

I have never applied a service pack to sql server 2000. Someone tells me that you have an option of supplying the SA password but do not have to and the service pack is applied just as if you did supply the SA password. This sounds odd to me. So, is it true? I am asking because our server instance shows "SP4" but a fix that was supposed to be included in SP4 was apparently not as the problem persists (link from sql server 2005 to 2000 fails when referenced in sql2005). I was thinking that whoever ran the service pack may not have provided the SA password so some of the SP4 was not applied?

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.

Thanks.

The service pack ask for the password, It allows to continue without a password, and it provides a notice about the dangers of so doing.

So yes, the service pack may have been properly applied without a password -IF the sa account did not have a password.

|||

There are two ways to log into a SQL Server. One is by specifing a login and password, such as the "sa" login. The other is by using the Windows Creditentials which the user has logged into the operating system with. When installing the service pack you have the option of logging in with which ever method you would like to; either the "sa" account or the Windows account.

What command is being performed when this error comes up?

|||

The above errors arise when trying to issue a select statement on the 2005 server which references tables on the 2000 server which it is linked to. I thought perhaps the person who applied the service pack 4 may not have done it correctly. I was not there, and have never done it myself. The message may imply missing components.

ex. On 2005 server: Select * From linked2000server.mydb.dbo.mytable

|||

Can you query any tables over the linked server, or is this one the only one causing the problem?

If it's every table a sp4 reinstall may be in order on the SQL 2000 server.

|||Nope!

Applying SP4. Do you have to provide sa password?

I have never applied a service pack to sql server 2000. Someone tells me that you have an option of supplying the SA password but do not have to and the service pack is applied just as if you did supply the SA password. This sounds odd to me. So, is it true? I am asking because our server instance shows "SP4" but a fix that was supposed to be included in SP4 was apparently not as the problem persists (link from sql server 2005 to 2000 fails when referenced in sql2005). I was thinking that whoever ran the service pack may not have provided the SA password so some of the SP4 was not applied?

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "s-1". The provider supports the interface, but returns a failure code when it is used.

Thanks.

The service pack ask for the password, It allows to continue without a password, and it provides a notice about the dangers of so doing.

So yes, the service pack may have been properly applied without a password -IF the sa account did not have a password.

|||

There are two ways to log into a SQL Server. One is by specifing a login and password, such as the "sa" login. The other is by using the Windows Creditentials which the user has logged into the operating system with. When installing the service pack you have the option of logging in with which ever method you would like to; either the "sa" account or the Windows account.

What command is being performed when this error comes up?

|||

The above errors arise when trying to issue a select statement on the 2005 server which references tables on the 2000 server which it is linked to. I thought perhaps the person who applied the service pack 4 may not have done it correctly. I was not there, and have never done it myself. The message may imply missing components.

ex. On 2005 server: Select * From linked2000server.mydb.dbo.mytable

|||

Can you query any tables over the linked server, or is this one the only one causing the problem?

If it's every table a sp4 reinstall may be in order on the SQL 2000 server.

|||Nope!sql

Applying SP1 on Sql Server Express production environment

How do you apply Service Pack 1 to Sql Server Express 2005?Hi,

this can be directly read from the setup instructions:

http://download.microsoft.com/download/b/d/1/bd1e0745-0e65-43a5-ac6a-f6173f58d80e/ReadmeSQLEXP2005.htm

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Applying SP to Log-shipping Standby server

Scenario: SQL2000 server that is the Standby server in a log-shipping scenario

The need: Apply SP4 to the standby server

Issue: Service pack installation wants all databases to be "writable". The databases on the standby server are "IsInStandby" state and cannot be made "writable" (non-Read-Only) by the Alter Database command.

Question: is there a way to alter the state so that I can apply the service pack without removing log-shiping, applying the sp and then reestablishing log-shipping?

ReadmeSql2k32sp4.htm:

http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm#_1462462_considerations_for_an_instance_i_gq59

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

Are there any guidelines or best practices for deploying or testing Service Packs? What types of tests should be performed prior to upgrading or do I just trust Microsoft?I always put them on test machines first for a month or so. Enough time that some applications have had a testing cycle, anyway. If there are no major complaints, the first production guinea pig is selected, and watched for a week. If no major problems come up, then the rest of the machines get it. never trust Microsoft. They are human, too...well, half human.|||That's the way to do it. We have dev, test, and production environments. Dev gets the release 1st, and if no one screams after a couple of weeks, we apply it to test. Again, the absence of any wailing and gnashing of teeth means we move to production.

Plus, an added benefit is that you get to practice the install in case something goes horribly wrong (oh my ... would that ever happen?)|||Definitely agree with the two previous posters. In addition, read the release notes, particularly if you have a clustered environment or one where you use replication (upgrades MUST be done in a certain order when replication is involved)

Lempster|||Thank you all!! Pretty much thought so, but just wanted to see how others were handling it. :)

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

When applying service packs to SQL Server (2000 or 2005), does the service
pack need to be applied to the client tools running on indivdual PCs/Laptops
as well? Thanks.
Hello,
May not be required to all desktops. You can install the service packs to
the desktop machines when ever there is a requirement araise.
Thanks
Hari
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? Thanks.
|||Please remember that DTS, as an example, is a client tool. If you have
different version of the client on your workstation and your server you run
the risk of getting totally different DTS packages and therefore results.
In SQL2005 the maintenance plans have changed dramatically between SP1 and
SP2. You cannot administer an SP2 created plan with an SP1 client. If a plan
is created on an SP2 server then you would not be able to maintain it from
an SP1 client.
Our rule is to update the client software when the client accesses an
updated server.
Chris
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? Thanks.

Applying Service Packs

When applying service packs to SQL Server (2000 or 2005), does the service
pack need to be applied to the client tools running on indivdual PCs/Laptops
as well? Thanks.Hello,
May not be required to all desktops. You can install the service packs to
the desktop machines when ever there is a requirement araise.
Thanks
Hari
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? Thanks.|||Hari Prasad wrote:
> Hello,
> May not be required to all desktops. You can install the service packs to
> the desktop machines when ever there is a requirement araise.
> Thanks
> Hari
> "MACason" <MACason@.discussions.microsoft.com> wrote in message
> news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
>
If you are running SQL2000 Analysis Services and upgrade to AS SP4,
you'll have to upgrade your AS Analysis Manager and client tools to SP 4
as well. If you don't do it, you'll get some strange errors when you try
to connect with the client tools. I'm not aware of any other scenarios
where it causes problems to run a lower SP level on the client, but
there might be...;-).
--
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Please remember that DTS, as an example, is a client tool. If you have
different version of the client on your workstation and your server you run
the risk of getting totally different DTS packages and therefore results.
In SQL2005 the maintenance plans have changed dramatically between SP1 and
SP2. You cannot administer an SP2 created plan with an SP1 client. If a plan
is created on an SP2 server then you would not be able to maintain it from
an SP1 client.
Our rule is to update the client software when the client accesses an
updated server.
Chris
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? 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.

Applying Service Packs

When applying service packs to SQL Server (2000 or 2005), does the service
pack need to be applied to the client tools running on indivdual PCs/Laptops
as well? Thanks.Hello,
May not be required to all desktops. You can install the service packs to
the desktop machines when ever there is a requirement araise.
Thanks
Hari
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? Thanks.|||Hari Prasad wrote:
> Hello,
> May not be required to all desktops. You can install the service packs to
> the desktop machines when ever there is a requirement araise.
> Thanks
> Hari
> "MACason" <MACason@.discussions.microsoft.com> wrote in message
> news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
>> When applying service packs to SQL Server (2000 or 2005), does the service
>> pack need to be applied to the client tools running on indivdual
>> PCs/Laptops
>> as well? Thanks.
>
If you are running SQL2000 Analysis Services and upgrade to AS SP4,
you'll have to upgrade your AS Analysis Manager and client tools to SP 4
as well. If you don't do it, you'll get some strange errors when you try
to connect with the client tools. I'm not aware of any other scenarios
where it causes problems to run a lower SP level on the client, but
there might be...;-).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Please remember that DTS, as an example, is a client tool. If you have
different version of the client on your workstation and your server you run
the risk of getting totally different DTS packages and therefore results.
In SQL2005 the maintenance plans have changed dramatically between SP1 and
SP2. You cannot administer an SP2 created plan with an SP1 client. If a plan
is created on an SP2 server then you would not be able to maintain it from
an SP1 client.
Our rule is to update the client software when the client accesses an
updated server.
Chris
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:1774B40F-32F8-4A01-8E4B-4C1C381FBE25@.microsoft.com...
> When applying service packs to SQL Server (2000 or 2005), does the service
> pack need to be applied to the client tools running on indivdual
> PCs/Laptops
> as well? Thanks.

Apply Service Pack Question

I have a couple of servers that are running SQL Server 2000 with no service
packs applied. I would like to apply SP3a to both of them. Are there any
possible/known problems that I should be aware of before attempting this.
This will be my first time doing this type of thing...
Thanks
EricMake sure you really read the documentation and readme etc that comes with the service pack! :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Eric Stewart" <stewarte@.repsilverstate.com> wrote in message
news:uB95tpeZDHA.2236@.TK2MSFTNGP10.phx.gbl...
> I have a couple of servers that are running SQL Server 2000 with no service
> packs applied. I would like to apply SP3a to both of them. Are there any
> possible/known problems that I should be aware of before attempting this.
> This will be my first time doing this type of thing...
> Thanks
> Eric
>

application service provider architecture

Hi Gang,
Not sure if this is the "right" sql server group to post this question in, but thought I'd give it the "ol'college try". If you feel that I may benefit by posting same question in a different group, please don't hesitate to let me know. Anyhow, below is my question:
Working with a client on developing an Application Service Provider system where users of the system will be affiliated with a parent company, the company will have the contract with my client and each company's data must be separated from all other companies. salesforce.com is a great example of such a structure, but has nothing to do with our application (meaning, we're not building a sales app).
What it comes down to is that there are 2 choices (unless someone knows of a others...)
1. Having the system create a separate database for each company or
2. Managing individual user access rights either through database roles or programatically.
We're looking at other DB providers to see what options we have, and for example, have found that Oracle 9i (which salesforce.com runs on) has a "Virtual Private Database Option". This feature enables developers to build 1 DB to support the ASP model and have Oracle logically (behind the scenes) manage access to the data. There are some disadvantages to doing this, but it is interesting.
I'm looking for information about / examples of SQL Server being used in an ASP environment and what methodology was used and why. Any assistance is greatly appreciated.
Thanks!I prefer separated databases... Each database can be modified, or
recovered, or backed up as the user sees fit... If they are all in a single
database and one user does something nasty and needs to rollback, it would
be a much more difficult task.. Security would be cleaner also. But you
would have lots of backup jobs to deal with...or maybe more complicated
jobs to backup everything.
However if there are going to be lots of customers, which would mean
hundreds or perhaps more databases, then the SQL tools do not work well...
It would take lots of time to open SQL Enterprise Manager for instance, so
you'd have to go command line probably...
The benefit of using a single database, is easier maintenance... But mixing
customer data might be a problem... would you have a separate set of tables
for each customer or simply add a companyid field to each table... IF you
have a companyID field added, you'd need to make sure performance wouldn't
suffer... If I have a small number of rows for my company in one of the
tables, where other companies have millions of rows, if I needed to do a
table scan, I'd have to look through everyone elses rows as well...
Tough choice,
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"appsprov" <anonymous@.discussions.microsoft.com> wrote in message
news:B973EF65-7175-4208-9657-3B8AAAADB2D5@.microsoft.com...
> Hi Gang,
> Not sure if this is the "right" sql server group to post this question in,
but thought I'd give it the "ol'college try". If you feel that I may
benefit by posting same question in a different group, please don't hesitate
to let me know. Anyhow, below is my question:
> Working with a client on developing an Application Service Provider system
where users of the system will be affiliated with a parent company, the
company will have the contract with my client and each company's data must
be separated from all other companies. salesforce.com is a great example of
such a structure, but has nothing to do with our application (meaning, we're
not building a sales app).
> What it comes down to is that there are 2 choices (unless someone knows of
a others...)
> 1. Having the system create a separate database for each company or
> 2. Managing individual user access rights either through database roles
or programatically.
> We're looking at other DB providers to see what options we have, and for
example, have found that Oracle 9i (which salesforce.com runs on) has a
"Virtual Private Database Option". This feature enables developers to build
1 DB to support the ASP model and have Oracle logically (behind the scenes)
manage access to the data. There are some disadvantages to doing this, but
it is interesting.
> I'm looking for information about / examples of SQL Server being used in
an ASP environment and what methodology was used and why. Any assistance is
greatly appreciated.
> Thanks!