April 2, 2010 at 11:26 am
CirquedeSQLeil (4/2/2010)
You could do it that way, or you could setup the replication with the snapshot and filters to get what you need. You would recreate it each time you need to rebuild dev
Genuinely confused here...isn't that what I said?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 11:32 am
Paul White NZ (4/2/2010)
CirquedeSQLeil (4/2/2010)
You could do it that way, or you could setup the replication with the snapshot and filters to get what you need. You would recreate it each time you need to rebuild devGenuinely confused here...isn't that what I said?
I interpreted it as a persistent replication. I think Chad did too.
If not, then yeah - the same.
I only wanted to emphasize that you would do the replication then remove it upon completion. Then re-establish it the next time you needed to sync.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 11:54 am
Jason,
Paul White NZ (4/2/2010)
I would be tempted to restore the production database to the staging server, and then run a script to set up snapshot replication with row (and possibly column) filters.
The script mentioned sets up replication each time the database is restored to stage, sorry I thought that would be clear.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 12:00 pm
Paul White NZ (4/2/2010)
Jason,Paul White NZ (4/2/2010)
I would be tempted to restore the production database to the staging server, and then run a script to set up snapshot replication with row (and possibly column) filters.The script mentioned sets up replication each time the database is restored to stage, sorry I thought that would be clear.
Paul
NP. I can C that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 12:16 pm
Ah. I missed that. I was thinking of setting up the Snapshot to run once per week, and then maintaining that publication as procs or tables were updated. Thanks for clarifying, autogenerating the replication script each time makes more sense. I'm thinking out loud now, but I'm a little nervous about relying on the snapshot to not miss a new object type that someone creates next month, so perhaps what I'll do is restore the DB, truncate all the tables, shrink to an appropriate size, then use the scripted snapshot just for the data. I like it. I'll see how the snapshot creation/transfer compares with the shrink time.
Thanks,
Chad
April 2, 2010 at 12:18 pm
Chad Crawford (4/2/2010)
Ah. I missed that. I was thinking of setting up the Snapshot to run once per week, and then maintaining that publication as procs or tables were updated. Thanks for clarifying, autogenerating the replication script each time makes more sense. I'm thinking out loud now, but I'm a little nervous about relying on the snapshot to not miss a new object type that someone creates next month, so perhaps what I'll do is restore the DB, truncate all the tables, shrink to an appropriate size, then use the scripted snapshot just for the data. I like it. I'll see how the snapshot creation/transfer compares with the shrink time.Thanks,
Chad
Keep us posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 5:33 pm
TheSQLGuru (4/2/2010)
1)3) consider getting Hyperbac. <....snip........>I even don't have to worry about promoting it here on SQLServerCentral any more since Red-Gate just bought them! :w00t:
Disclaimer: I have a close relationship with Hyperbac, use their products and recommend them to my clients. Also, if you care to you can mention TheSQLGuru sent you you will get a discount and my daughter will get a few coins for her college fund. Feel free to contact JAven@hyperbac.com.
did you say Red-gate have bought Hyperbac? Don't Red-Gate already have their own backup compression tool?
Do hope this will not reduce competition and the number of options on the market.
---------------------------------------------------------------------
April 2, 2010 at 5:41 pm
george sibbald (4/2/2010)
TheSQLGuru (4/2/2010)
1)3) consider getting Hyperbac. <....snip........>I even don't have to worry about promoting it here on SQLServerCentral any more since Red-Gate just bought them! :w00t:
Disclaimer: I have a close relationship with Hyperbac, use their products and recommend them to my clients. Also, if you care to you can mention TheSQLGuru sent you you will get a discount and my daughter will get a few coins for her college fund. Feel free to contact JAven@hyperbac.com.
did you say Red-gate have bought Hyperbac? Don't Red-Gate already have their own backup compression tool?
Do hope this will not reduce competition and the number of options on the market.
http://www.simple-talk.com/opinion/opinion-pieces/inside-red-gates-hyperbac-acquisition/
More info on the acquisition.
I did like RedGates backup solution though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 6:10 pm
sorry Chad getting completely off subject but I know these companies pick up on internet entries so I will post this here:
Hyperbac - please don't move away from the ease of use principle of your backup compression tool and the ability to continue to use SQL native code and utilities and thereby not have to change existing practices. I am a DBA, I don't need another GUI front end, I have SSMS and SQL, and I don't want to have to learn a new set of commands to do backup and restore (my base, most important function), or learn how to interpret another set of error messages and numbers.
My 2 cents worth.
---------------------------------------------------------------------
April 2, 2010 at 6:52 pm
George, I think you would be ok with RedGate Backup. It's not like most other backup tools for SQL server. It integrates nicely with SQL server and iirc it permits the use of standard commands. It is pretty straight forward and simple to use. I think that is why the two are able to merge.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2010 at 8:50 pm
CirquedeSQLeil (4/2/2010)did you say Red-gate have bought Hyperbac? Don't Red-Gate already have their own backup compression tool?
Do hope this will not reduce competition and the number of options on the market.
http://www.simple-talk.com/opinion/opinion-pieces/inside-red-gates-hyperbac-acquisition/
More info on the acquisition.
I did like RedGates backup solution though.
[/quote]
That is very interesting - so, just to get this straight:
The guys who wrote SQL Litespeed sold it to Quest. Very good product and well worth it.
These same guys then go out and develop Hyperbac...another very good product and well worth it.
Yeah - I have used both and can recommend either of them.
So, now these same guys sell Hyperbac to Redgate (a competitor of Quest) - at least in the backup realm. I personally use Redgates comparison tools - but have not used the backup tools.
Do I have this right?
Any bets on what these guys are going to build next, market - make successful and end up selling to Idera? :hehe:
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 3, 2010 at 12:33 am
Jeffrey Williams-493691 (4/2/2010)
Yeah - I have used both and can recommend either of them.
Have used LiteSpeed quite extensively, but never HyperBac. The thing I don't like about LiteSpeed is its use of extended stored procedures :sick:
SQL Server 2008 compression has taken away my reasons for using a third-party tool anyway - and apparently R2 extends backup compression to Standard Edition, which is very welcome.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 12:38 am
Chad Crawford (4/2/2010)
I'm thinking out loud now, but I'm a little nervous about relying on the snapshot to not miss a new object type that someone creates next month
Yes, that makes sense Chad. In mitigation, I would say that (a) you could automate the inclusion of new objects based on your existing audit trail for new objects; and (b) doesn't the existing arrangement require you to write a script to 'filter' rows for new objects anyway?
I think the snapshot idea is fascinating, though I can quite appreciate that the backup/delete/shrink approach might be quicker to implement. Oh no, look, "it depends" 😉
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 1:42 am
Not sure if this has already been mentioned, but another alternative (using a similar approach to snapshot replication) would be to set up an SSIS package. This could be made to iterate over a list of tables and filter conditions to push the selected rows to the target databases.
This retains the advantage of skipping the delete and shrink operations, and does not need to be set up on stage each time. The SSIS approach can take advantage of minimally-logged bulk copy, but would require that scripts be run on the target servers to reflect any DDL changes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 2:20 am
Paul White NZ (4/3/2010)
Jeffrey Williams-493691 (4/2/2010)
Yeah - I have used both and can recommend either of them.Have used LiteSpeed quite extensively, but never HyperBac. The thing I don't like about LiteSpeed is its use of extended stored procedures :sick:
SQL Server 2008 compression has taken away my reasons for using a third-party tool anyway - and apparently R2 extends backup compression to Standard Edition, which is very welcome.
Personally not a fan of Litespeed. I do like RedGate Backup though. There are a bunch of others out there that do rank lower than litespeed for me (Tivoli).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply