Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server files & filegroups

Posted on 2013-02-07
8
Medium Priority
?
241 Views
Last Modified: 2013-02-07
Hi

We have a database that is growing quite large with legacy data, we are archiving the old data into archive tabes within the same database. The data has to be available for reporting.

It has been suggested that these archive tables can point to different files than the main database.

How is this done - I can create a new file, but how do I get the data from the archive tales to use it.

Andy
0
Comment
Question by:Andy Green
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38863894
steps:
1) create a new filegroup
2) create a new file into this filegroup
3) move the table to the new filegroup
  => to perform that step, you use the "clustered index trick"
  a) if the table has already a clustered index: drop it, and recreate it and specify the new filegroup
  b) if the table does not have a clustered index, create a clustered index on the filegroup
0
 
LVL 3

Author Comment

by:Andy Green
ID: 38863926
I have done as you suggest, yet when I do a properties on the table, the storage still says Primary not Archive as I'd expect.

If I look at the properties for the index that too says Primary.

Andy
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863942
then you did not create the clustered index correctly.
please show how you created the index
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 3

Author Comment

by:Andy Green
ID: 38863964
Opened the table in design mode
Clicked TableDesigner
Clicked Index Keys

from the pop up I clicked Add
In the section Data Space Specification I selected Archive.

If I go back into this it says Archive, its just the properties that report wrong.

I have refreshed.

Andy
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38863989
you have to "SAVE" those changes, otherwise it will not be effective.
0
 
LVL 3

Author Comment

by:Andy Green
ID: 38864039
Sorted it, rather than open the table in Design, I added the index by opening the table dropdown | Indexes | Add.

Andy
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38864061
good :)
glad I could help
0
 
LVL 3

Author Closing Comment

by:Andy Green
ID: 38864078
Thanks for your help

Andy
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question