Tuesday, March 27, 2012

Best schema for replicate a DB without replicate Data

Hi,
We had log shipping from LA to New York for our mission critical databases.
However, the log shipping breaks all the time because some of these database
s
or tran logs are huge.
Afer re-evaluation we found that some DBs we don't really need the data
replicated but we do need to keep the schema and objects up to date:
everything except the huge volume of data. It does not need to be real time,
we could do this once a day.
Could someone suggests us what are the better options of doing this? Some
way that is simple without a lot of maintenance issues and potential breakin
g
points.
Thanks
HuiSeems you simply want to generate schema at regular intervals?
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||check out DB Ghost - http://www.dbghost.com
"Christine C" wrote:

> Hi,
> We had log shipping from LA to New York for our mission critical databases
.
> However, the log shipping breaks all the time because some of these databa
ses
> or tran logs are huge.
> Afer re-evaluation we found that some DBs we don't really need the data
> replicated but we do need to keep the schema and objects up to date:
> everything except the huge volume of data. It does not need to be real tim
e,
> we could do this once a day.
> Could someone suggests us what are the better options of doing this? Some
> way that is simple without a lot of maintenance issues and potential break
ing
> points.
> Thanks
> Hui
>
>|||Sorry, I did not describe the full requirement:
1. We need to copy all changes in the databases (including sysusers, stored
procedures, views, udf, ...etc) except the data. Of course sysusers we do
need the data because we need all the users replicated.
2. One challenges we have is that there are objects (views, stored proc)
that are cross databases. Therefore whatever tools we used need to be able t
o
not error out because of the dependencies.
3. One of our developers are trying the SQL-DMO APIs to do the
copyalltables. Here are the uncertains for us:
a. He is not sure if SQL-DMO allows him to copy the users, views, and
stored procedures separately as well.
b. Tthe entire extract for replicating three databases (without data) took
50 minutes. Which means the entire time the destination databases are going
to be unusable for a long time.
Common question to all tools in your link:
c. what we need is for the tool to take care of the delta of the source and
destination. The objects that no longer exist in the source, how do we remov
e
them in the destination if the scripts are driving from the source?
d. The tools suggested in your link, are they mostly scripting tools for
DDLs only (we don't care the data) or some of them can transfer users as
well?
e. Do they require manually adding or changing the script defination
everytime a DB has changed (schema or stored proc)? Can those tools also kno
w
to remove all objects before it proceeds?
I know the list of questions are long, but these are critical processes we
want to setup in production. The problems that we encountered might not be
standards and having huge databases need to replicate to New York for DR
complicates the issue too.
Thank you for your patience in replying.
"Tibor Karaszi" wrote:

> Seems you simply want to generate schema at regular intervals?
> http://www.karaszi.com/SQLServer/in...rate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Christine C" <ChristineC@.discussions.microsoft.com> wrote in message
> news:87C67A1A-52DF-4069-AD9B-A8637ADD3B35@.microsoft.com...
>
>

No comments:

Post a Comment