April 8, 2014 at 8:00 am
We are in the process of moving about 20 databases to a new data center. As part of that move, we want to consolidate and clean up our environment. That means eliminating some databases, and rearranging some tables and columns to take advantage of new data sources. We want to make the data center move as quickly as possible to take advantage of the new infrastructure, so the cleanup effort is going to be done after the move.
This is for a BI environment, so there are SSRS reports and Sharepoint dashboards that source these databases.
Anyone have any ideas or approaches for managing this process? We would like to use extended properties or something else to flag tables and columns that will be removed, so that we can eventually find and clean them up. But until that happens, we need the existing SSRS reports and dashboards to keep working until we can get them switched over to the future state schema.
The environment is SQL Server 2008R2 Enterprise; the SSRS reports are written with Visual Studio 2012.
Any thoughts or suggestions on this are appreciated...
Sean
April 8, 2014 at 11:08 am
You could create table(s) to hold info about changes that need done in the future.
Since these will be temporary/transition tables, you wouldn't need to spend a huge amount of time designing them, but I would spend a little time -- 2 hours or so for a trained designer should do it -- to get reasonable logical and physical design.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 8, 2014 at 11:35 am
Thanks Scott. Our lead developer likes tracking in the database to keep everything accessible via query so that is definitely an option for us. Any thoughts on ways to give us visibility/awareness as SSRS reports send queries to the DB engine that utilize one of the impacted tables/columns?
The challenge is that as we modify/remove objects from the schema in the new environment, we want to be proactive in first updating reports and dashboards to point to the appropriate new object. That way we can refactor those reports/dashboards before we modify/remove objects, so that our users never see errors or report failures.
I wasn't sure how much visibility the extended properties have in query logging, just hoping for something that would let us check for utilization against the impacted tables before we apply changes...
May 29, 2014 at 6:59 pm
To find tables to remove .. I am assuming you want those less/most used to be flagged somehow?
This tells you the tables most accessed
SELECT
t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups) AS 'Total accesses',
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
FROM
sys.dm_db_index_usage_stats i RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name
HAVING ISNULL(SUM(i.user_seeks + i.user_scans + i.user_lookups),0) > 2
ORDER BY [Total accesses] DESC
I would have copies of the ssrs reports published that point to the new source and leave the old ones in place so as to have a backout in case things dont go well.
----------------------------------------------------
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply