SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 2000 to 2008 DB Transafer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbalearner
Constraint Violating Yak Guru

256 Posts

Posted - 03/22/2013 :  08:26:11  Show Profile  Reply with Quote
I have database size 250 GB in 2000 Server is going to restore on 2008 server.

However this is ok in getting restored and even changing its compatibility and collation from Latin1_general_CP1_CI_AS to SQL_Latin1_CP1_CI_AS. Everything works fine.

Since the feature of Compressing the database is to be implemented.

Could anyone help me in let me know as to how to compress the database / Tables underlying.

Many thanks

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 03/22/2013 :  08:39:00  Show Profile  Visit russell's Homepage  Reply with Quote
start here
Go to Top of Page

dbalearner
Constraint Violating Yak Guru

256 Posts

Posted - 03/23/2013 :  04:53:06  Show Profile  Reply with Quote
Thanks For the Article which I have gone through which is very generic in adopting to Data Compresssion.

As per my query the database has got couple of tables with Views and Stored Procedures and currently in 2000 Server.

I have the DB.BAK and restored on the 2008 R 2 Version, however, I need to get some clarifications as to how I should compress the database (ROW OR PAGE).

I have started with drawing the statistical information using to determine on changing compression state will affect a table or an index, using sp_estimate_data_compression_savings.

Now unsure based on this result - should I use the ROW or PAGE compression.

Should I apply either PAGE / ROW based then what are the possible measures / repurcussions?

Is there anything that I will be missing while doing this upgrade from 2000 to 2008 R 2?

Your expertise highly appreciated and many thanks.

Go to Top of Page

dbalearner
Constraint Violating Yak Guru

256 Posts

Posted - 03/25/2013 :  00:18:11  Show Profile  Reply with Quote
While the Code below implemented after restoring the Database from 2000 to 2008 R 2 platform, haven't changed any.


USE [IMAX007]
GO

/****** Object: Index [PK_IA_NOCOM_AE] Script Date: 03/25/2013 13:36:35 ******/
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[IA_NOCOM_AE]') AND name = N'PK_IA_NOCOM_AE')
ALTER TABLE [dbo].[IA_NOCOM_AE] DROP CONSTRAINT [PK_IA_NOCOM_AE]
GO

USE [IMAX007]
GO

/****** Object: Index [PK_IA_NOCOM_AE] Script Date: 03/25/2013 13:36:36 ******/
ALTER TABLE [dbo].[IA_NOCOM_AE] ADD CONSTRAINT [PK_IA_NOCOM_AE] PRIMARY KEY CLUSTERED
(
[AE_TITLE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 20) ON [PRIMARY]
GO


ALTER TABLE IA_NOCOM_AE
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO


There is no change in the compression.

Any Suggestions as how to compress, absolutely no idea.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000