May 4, 2011 at 5:10 pm
That right there is key. Have you considered putting all your procs and the like that you need to support this with into a 'coding' database, and simply backup/restoring the primary to the new server, and having all the 'coding' items look into the restored DB?
That's an idea, although I hate writing T-SQL that has to constantly refer to objects in another database. Being visual, I like the various GUIs that SSMS offers to build views, queries, etc., but they don't work if the tables in question are in another database. One has to actually start typing. Ech. 😀
FWIW my procedure finished copying tables in a little under two hours; I'm thinking that's the way to go. Here's what the job does:
- Runs my proc to backup scripting for all indexes unique to the DW database
- Runs my proc to drop and copy all tables updated within the past 1.25 days
- Runs my proc to recreate all DW-specific indexes
It's a start.
May 5, 2011 at 12:29 am
I may have missed it in the posts above... forgive me if I did.
If you have a SAN, it frequently comes with (or is an optional purchase that's well worth it) the ability to do what I call a "SAN SNAPSHOT" and is NOT to be confused with a snapshot in SQL Server. We had a reporting server at my old company just like you. We used to also maintain a terabyte update every night at midnight (as well as on demand) and it would take about 2 minutes with no degredation on the production server and only a 2 minute outage to make sure a terabyte of data from a high volume production system was all up to snuff on the reporting system. It absolutely SMOKES.
--Jeff Moden
May 5, 2011 at 12:22 pm
>>Here's my situation: I have to refresh table data nightly; however I need to leave everything else (procedures, views, stored procedures) intact.
Maybe you can script out all the views and stored procedures. Then restore a backup. Then drop your just restored views and stored procedures. Then run your script to re-create the views and stored procedures.
May 5, 2011 at 12:28 pm
Thanks again to all who responded. We had a meeting this morning and basically threw out the whole project. [g]
Don't worry, I'm still employed - we just are taking a different tack.
We got so mired in the details we forgot what our original issue was. It was not performance, it was modularity. We realized that all we wanted to do was to move all report-related objects out of the production database so that reporting would survive in the event of an upgrade of our main database application.
So we scrapped the data warehousing part; I just created a database with views that point to tables in production! I'm now in the process of redirecting all reports to that database. As reports come up that use custom-built views, I move them over too, reprogramming as needed. If a report runs slow, I feed it with a custom-built stored procedure instead.
So I'm going to close this thread, with sincere thanks to everyone who chimed in. Maybe I will be doing a real data warehousing project some day -- but not today!
Best regards to all,
Barry
May 5, 2011 at 1:59 pm
bimplebean (5/5/2011)
I just created a database with views that point to tables in production!
If, by chance, users are allowed to write their own reports against such things, you may be opening another thread quicker than you think. 🙂
--Jeff Moden
May 5, 2011 at 2:02 pm
Heaven forbid! [g]
May 7, 2011 at 11:08 am
bimplebean (5/5/2011)
We realized that all we wanted to do was to move all report-related objects out of the production database so that reporting would survive in the event of an upgrade of our main database application.So we scrapped the data warehousing part; I just created a database with views that point to tables in production!
I don't mean to drag a thread out, Barry, but I have to ask... aren't the two bolded snippets of text in the quote above a bit contrary to each other?
--Jeff Moden
May 9, 2011 at 8:45 am
Hi,
You could have a database that holds your code which uses Synonyms to the 'real' tables. We have done this several times and it works great. That way you just restore your database and off you go.
May 9, 2011 at 9:06 am
Jeff:
Our main system supports Epicor Vantage; our goal has been to get all non-Vantage items out of that database. That includes report-specific views, user-defined functions, stored procedures, etc.
If we update or rebuild the database from scratch, all that goes away. We wanted a separate repository for our report-specific database objects. Now we have it.
May 9, 2011 at 9:12 am
SYNONYMS. Bloody brilliant! That way my views aren't clogged up with references to tables. Thanks!
May 9, 2011 at 9:14 am
They are a frequently over looked feature. We have also used them for archiving when we have a vendors database - the application has no idea the data no longer resides inside the database and we can always add it back in if we need to. 😉
May 9, 2011 at 9:40 am
I had created a stored procedure that built a view for every desired table. I reworked it to create a synonym instead. So far everything else seems to be working. This is a great idea, thanks a lot!
May 9, 2011 at 9:50 am
Glad to be able to help.
June 29, 2011 at 8:31 am
Maybe too late now it's end of June. If you are copying all tables (800) then there is a simple SSIS task that copies all tables from one database to another, you don't need to select them manually, but it does all tables, you can't select which ones you need. Use the SQL Server Object Task, and choose the All Objects option. But I think the 2 database need to have the same schema, you can get away with some tables not being there though.
Panos
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply