Moving SQL installation to a new Partition

Hello,
I have a SQL 2000 installation on a small partion that I would like to
move to a second larger partion on the same disk array array with the
config as follows:
d: full installation of prod and test instances with data and log files
100 GB
e: empty partion with 204 GB which I want to move the "whole thing"

I did an Xcopy of all files,permissions and attributes, checked for
errors, then I changed drive
letters and restarted. Only one instance came up (the other threw an
error of: MSSQL,,the system cannot find the file specified.

I realize that I could detatch the DB's and move them leaving the base
install on the old partition however I would like to simulate the loss
of the disk array and the restore to said array from a backup.
(ultimately I want to delete the 2 partitions and restore from tape to
a single 300GB d: partition)

I am trying to buy time and avoid a re-install of SQL untill I can get
a whole new platform.
I can work it out on my own if someone can point me in the right
direction. I suspect a registry entry to a disk id (partition)Marc C (mchang@.allosource.org) writes:

Quote:

Originally Posted by

Hello,
I have a SQL 2000 installation on a small partion that I would like to
move to a second larger partion on the same disk array array with the
config as follows:
d: full installation of prod and test instances with data and log files
100 GB
e: empty partion with 204 GB which I want to move the "whole thing"
>
I did an Xcopy of all files,permissions and attributes, checked for
errors, then I changed drive
letters and restarted. Only one instance came up (the other threw an
error of: MSSQL,,the system cannot find the file specified.
>
I realize that I could detatch the DB's and move them leaving the base
install on the old partition however I would like to simulate the loss
of the disk array and the restore to said array from a backup.

Good! You've just done that and found that it does not work!

OK, assuming that you had stopped SQL Server prior you started copying,
the operation as you described it, should work I think. But if there is
a file you missed a long the way there may be trouble.

But for restoring from backup... You can't really just back up the disk
drive as such, you need to use SQL Servers own BACKUP command, so that
backups are consistent. (Well, if you stop SQL Server before you start
the backup, you might get away with it, but normally you don't want to
do that.)

Quote:

Originally Posted by

I am trying to buy time and avoid a re-install of SQL untill I can get
a whole new platform.
I can work it out on my own if someone can point me in the right
direction. I suspect a registry entry to a disk id (partition)

I don't think there is any. Rather I would look at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\Parameters
as the prime suspect.

Exactly where do you get this error?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your reply. I get the error on boot/startup. It's possible I
had a file open during the copy so the sql service cannot find a file
at startup. I will look into the logs for more detailed errors when I
try again during my next maintenance window. however the sql errorlog
shows no entries during that time span -so it's likely sql never
starts.

The only record of the failure was in the System Event log event id-
7000 Source- Service Control Manager with teh message The
MSSQL$Instancename service failer to start due to the following error:
the system cannot find the file specified.|||Marc C (mchang@.allosource.org) writes:

Quote:

Originally Posted by

Thanks for your reply. I get the error on boot/startup. It's possible I
had a file open during the copy so the sql service cannot find a file
at startup. I will look into the logs for more detailed errors when I
try again during my next maintenance window. however the sql errorlog
shows no entries during that time span -so it's likely sql never
starts.

Hm, that would indicate that a DLL or somesuch is missing rather than a
database file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Moving Reporting Services

Hi,
I have two websites built on same box using w2k3 server. The second
website uses port 8020 in order to access it. How can I make my second
website be able to use Reporting services that are installed on the first
website? Can I copy over the important dll's to second site and register
them? How do I do this if possible? What needs changed in configuration
settings for Reporting services?
Thanks,
JJMultiple instances of Reporting Services in a single server are not
currently supported. There are a couple of articles around to work around
this but in general, you will need to copy everything in the ReportServer
directory and modify the RSReportServer.config file.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"JJ" <jjmraz@.-NOspAM*hotmail.com> wrote in message
news:e$vNvaiAFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have two websites built on same box using w2k3 server. The second
> website uses port 8020 in order to access it. How can I make my second
> website be able to use Reporting services that are installed on the first
> website? Can I copy over the important dll's to second site and register
> them? How do I do this if possible? What needs changed in configuration
> settings for Reporting services?
> Thanks,
> JJ
>

Moving Records for Archiving

I've got two tables, one is an archive of the second (tables are identical). I'd like to migrate records from one to the other (as in, move, insert into one while deleting from the other).

I know I can start a transaction, do an INSERT INTO...SELECT, followed by a DELETE, check rows affected, then closing with a commit transaction (or rollback if the counts don't match), but it seems as though I might be over thinking it. Is this considered the optimal approach?No, not really. An alternate approach might be to insert into the historical tables with an insert date and update "current" table with an active flag. Your OLTP will always look for the active flag.

If you use this approach, you're not deleting thousands of rows out of your tables, messing up your statistics, and blowing holes in your indexes during the middle of the day.|||Right. I did write the an SP to UNION the two tables (origin and archive) if I needed to include history in my queries, and I don't much like it, so I can see what you mean. Just add an Archived flag to the origin table, then run a process regularly to insert non-archived records into the historical archive. In which case, I'll need to include that Archived flag in my indexes for optimization.

If I go this route though, now that I reflect, I won't actually need an archive table (unless I simply want a physical backup - which is irrelevant with our nightly backup procedures). I suppose I could offload historical queries to the archive table, but historical research is rarely done, so the savings to the origin table would be negligable. With that, I'm going to have to re-evaluate my need for a second 'archive' table. Assuming historical research is not a regular activity, can you think of a reason why it would be beneficial to maintain a separate archive table if I implement an archive flag within the origin table?|||You didn't mention volume...but I would say the indicator won't optimize well

Also I imagine there would be more Update activity on the current and none on the history...so they might have different indexing strategies...

MOO

I'd go with 2|||Volume.. right.. my bad. Currently there's a low amount of data (only about 8k records), but this will increase as the application is used (these records represent transactions sent to our payroll system, so you can imagine this growing quite large).

So, Brett, you believe that two tables doing an INSERT INTO...SELECT and a DELETE wrapped in a Transaction would be optimal? Or is there an alternative SQL query that could accomplish the same job? I do understand the though about different indexes, it does make sense.

I believe I'm just concerned that all the INSERTs execute, and the DELETEs don't even begin until after the INSERTs complete. Although, with a transaction, the issue is moot I suppose, since all of the INSERTs and DELETEs can rollback if an issue is encountered. I realize I could do a 1-to-1 migration, but it would require starting and committing a transaction for every record being archived, and that doesn't seem efficient.|||Well, I guess if you want to track historical views of the data (a common practice), I would use a trigger to move to historical data, so I would not be bound to anyone process...

With that said, it sounds like you only want to keep the last change...right?

I would keep them all...

What Business need is this meant to support?|||If you are going to delete from the production table, your delete statement should include a link to the archive table on the primary key. That way, you ensure you do not delete any records which have not been moved to the archive table.

delete
from production
inner join archive on production.pkey = archive.pkey|||Ok.. The requirement is that I keep a record of the transaction sent to payroll for umpteen years. To smooth this out a bit, I was going to archive anything over 12 months into an archive table, removing the original record from the original table once it had been moved.

I figured as the original table grows, queries against the original table will take longer unless I do something. Hence, the creation of the archive table. Now, historical research is only done by administrators of the system, so I'm not worried about that, I'm more concerned about the primary transaction table since it's hit pretty frequently. So I figured I'd offload transactions over 12-months to the historical table to try to keep things clean, but I wanted to determine the most safe and efficient means of migrating those records.

If I have to do it in two queries, that's fine, I just wasn't sure if I was on the right track.

Blindman.. I like that delete, I didn't even think of doing it that way.

BEGIN TRAN T1
INSERT INTO tblTransArchive SELECT * FROM tblTrans WHERE dtTimeStamp < DATEADD(m, -12, GETDATE())
DELETE tblTrans FROM tblTrans T INNER JOIN tblTransArchive TA ON T.iTransID= TA.iTransID
COMMIT TRAN T1

This code works, and I'm fine with it, as long as it's the optimal way to do it.|||Here's the SP I created based around that:

CREATE PROCEDURE [dbo].[spArchiveTrans] AS

DECLARE @.InsertCount int
DECLARE @.DeleteCount int

BEGIN TRAN T1
INSERT INTO tblTransArchive SELECT * FROM tblTrans WHERE dtTimeStamp < DATEADD(m, 0, GETDATE())
SET @.InsertCount = @.@.ROWCOUNT
IF @.@.ERROR = 0
BEGIN
IF @.InsertCount > 0
BEGIN
DELETE tblTrans FROM tblTrans O INNER JOIN tblTransArchive OA ON T.iOrderID = TA.iOrderID
SET @.DeleteCount = @.@.ROWCOUNT
IF @.@.ERROR = 0
BEGIN
IF @.InsertCount = @.DeleteCount
COMMIT TRAN T1
ELSE
BEGIN
ROLLBACK TRAN T1
RAISERROR('Archive totals did not match. Transactions rolled back.', 16, 1)
END
END
ELSE
BEGIN
ROLLBACK TRAN T1
RAISERROR('Error removing transactions from origin. Transactions rolled back.', 16, 1)
END
END
ELSE
BEGIN
--Nothing to do
ROLLBACK TRAN T1
END
END
ELSE
BEGIN
ROLLBACK TRAN T1
RAISERROR('Error inserting transactions into archive. Transactions rolled back.', 16, 1)
END
GO|||Why don't you run a test with that archive flag idea first? Compare that to the performance issues all this moving of data and testing is going to cause you. You really want to nail this thing the first time around, or you'll be paying for it later.|||Yeah.. I'll give that a shot. My only concern with the archive flag is that the data will always be there intertwined with the active data. So every request for active data will have to sift through archived records. If I run a cleanup procedure every night, the performance impact is neglegable. Alternately, I'll have to find all the queries that hit the original table and add another flag to the query to ensure it pulls the correct columns, which I'm not complaining about, just concerned about missing one.