Database Unit Testing: February 2012

Saturday, February 25, 2012

Database Replication

Hi,
I'm learning Database Replication. what's the Database Replication for? what
applications use this feature? Please advice.Hi
Have you read the section "Introducing Replication" in Books Online?
Hillary Cotter has a book on replication
http://www.nwsu.com/0974973602_.html which may help if you proceed along
this route.
John
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||For having the same (or nearly the same) data in different places.
The most obvious example is a salesperson wanting to have a subset of the
the sales database on his laptop with the ability to make changes locally
and then have those changes get synchronized into the corporate database
when he re-connects to the LAN.
Also frequently used in Reporting Server situations, where the main
transactional database is too busy to serve up large queries for reports.
One way replication ships the data to a db that the reports are run off.
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||Thanks all...|||"Kevin3NF" wrote in message
> For having the same (or nearly the same) data in different places.
> The most obvious example is a salesperson wanting to have a subset of the
> the sales database on his laptop with the ability to make changes locally
> and then have those changes get synchronized into the corporate database
> when he re-connects to the LAN.
> Also frequently used in Reporting Server situations, where the main
> transactional database is too busy to serve up large queries for reports.
> One way replication ships the data to a db that the reports are run off.
>
Thanks Kevin, There are good sample, easy to understand.
How about Analysis Services? Can you please give me some samples too.|||When the CEO wants to know how many widgets were sold in the NorthEast
district in Q4, 2004...he's going to open up Excel, connect to the Analysis
Services database and select those parameters. Very easy and he doesn't
have to know any programming.
Lots of work goes into making that AS database, form bringing the data in,
to getting it ready for the boss to start slicing and dicing.
I've never played in that area, so my knowledge is limited. I am pitching a
project that involves this, so ping me in early 2006...I should be much more
well-versed by then :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:uqiB5q1TFHA.3584@.TK2MSFTNGP14.phx.gbl...
> "Kevin3NF" wrote in message
> Thanks Kevin, There are good sample, easy to understand.
> How about Analysis Services? Can you please give me some samples too.
>
>|||Thanks Kevin...

database replication

I have one database(database1) in houston and the other database(database2) at Newjersy. SO if i wanted to replicate the data from the database1 to database2 how can I do that. I wanted to setup the replication to get the data into only one table. Infact both the tables are not identical in structure.

Thanks.There are several flavors of replication. Snapshot being the simplest, and which may work for you if the NJ data is read only. Snapshot rep is used mainly for bulk data movement, once a day, once a week type stuff. It is also used as a first step for the other rep types; transactional and merge replication.

Transaction rep, as it's name suggests, transfers data much more often. I don't think it's upon every db transaction but it's sure close to it.

Merge replication is when the folks in NJ need to update the data. This can get dicey.

BOL has very good info on replication.

Database Replication

I'm a newbie so bare with me. If I have two servers running SQL Server, a primary and a secondary, how can I make sure that both databases (One on each server) are always syncronized? In our situation the secondary server is a hot backup in case the primary fails. If the primary fails then data gets written to the secondary. When the primary comes back up then its database needs to be synced with the secondary. Can anyone tell me how to do this?Have a look on books online about Transactional Replication...

This is a form of replication that will make your two server in sync.

Any changes that are made on one server are automatically replicated over to your other server.

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.sql-server-performance.com/transactional_replication.asp

Hope this helps mate|||Thanks for the links! Do you know if you are doing transactional replication, if the other server is down, will the server que the transactions and keep trying to send them until it succeees?|||no,

As far as I know it will publish on an all or nothing basis.

If your primary server goes down then replication will stop because it has no server to replicate to (i.e no subscribers)

This is the time you flip the primary to your hot spare and fix the original server. After you have sorted the problem on the original server just kick off replication again.

Cheers|||Why don't you create a 2 node cluster?|||Not using Enterprise. I don't think it is available on standard.|||You're right, Only on Enterprize.

Database Replication

I follow the considerations definied by document, but I have an error
when I insert data into a table, because it has an identity field, and
the replication return the following error:
An explicit value for the identity column in table 'SAR_Callejero' can
only be specified when a column list is used and IDENTITY_INSERT is
ON.
Then, I modified the insert sp and added the following:
SET IDENTITY_INSERT sar_callejero ON
But newly gave the same error.
Can you help me?
Thanks
Is your insert supplying an identity value when it shouldn't?
"cbaffigi@.apsf.com.ar" wrote:

> I follow the considerations definied by document, but I have an error
> when I insert data into a table, because it has an identity field, and
> the replication return the following error:
> An explicit value for the identity column in table 'SAR_Callejero' can
> only be specified when a column list is used and IDENTITY_INSERT is
> ON.
> Then, I modified the insert sp and added the following:
> SET IDENTITY_INSERT sar_callejero ON
> But newly gave the same error.
> Can you help me?
> Thanks
>

Database Replication

we have setup a sql2005 server for reporting. we want to replicate the
transaction databases which used for live applications to that server such
that all reports will be generated in that server.
This reporting server is supposed read-only and with let's say 30 mins delay
from production data.
With this requirement, should we use the transactional replication or there
any method?
Thanks,
Ryan
Ryan,
transactional replication is often used for this type of reporting
requirement. You could also enhance the system by using the snapshot
committed isolation levels to maintain access while the distribution agent
is running. The main 'competitor' technology on SQL Server 2005 is database
mirroring with database snapshots. There's no detailed list of pros and
cons, but as I'm a replication guy I'll point out that mirroring doesn't
support FTI and you can't take back ups of snapshots

Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul-
I have client who maintains a local SQL 2005 database which has
numerous bulk updates applied throughout the day. They wish to
replicate (most of) this data to their web host which is in another
city. They are trying to decide whether to use Transactional
Replication or once-per-day Merge Replication. (Concurrency is not a
big issue here).
What method would you recommend? What are the most important
considerations?
Thanks,
Paul
Paul Ibison wrote:
> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots

> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Merge is generally slower. If there are numerous updates to the same row,
then it can approach transactional times, but I have rarely seen cases of
someone claiming it to be faster. It's geared up for offline updates at teh
subscriber and conflict resolution, neither of which you'll need. I'd
definitely go with transactional.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks, Paul!
Does Transactional require an "always on" connection to the subscriber?
Paul Ibison wrote:
> Merge is generally slower. If there are numerous updates to the same row,
> then it can approach transactional times, but I have rarely seen cases of
> someone claiming it to be faster. It's geared up for offline updates at teh
> subscriber and conflict resolution, neither of which you'll need. I'd
> definitely go with transactional.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No - as long as there is a connection when the distribution agent is
scheduled to run you're ok (different for immediate updating subs but not
relevant in your case).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul, thanks a lot!
Do you know any limitation on using transactional replication? eg the min
delay time, can it perform query during the replication.
Aslo how's the overhead on resources of compare to mirroring? Is it require
many resources (eg. CPU and RAM) during the processing?
Regards,
Ryan
"Paul Ibison" wrote:

> Ryan,
> transactional replication is often used for this type of reporting
> requirement. You could also enhance the system by using the snapshot
> committed isolation levels to maintain access while the distribution agent
> is running. The main 'competitor' technology on SQL Server 2005 is database
> mirroring with database snapshots. There's no detailed list of pros and
> cons, but as I'm a replication guy I'll point out that mirroring doesn't
> support FTI and you can't take back ups of snapshots

> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Ryan,
I've been using transactional replication at my current employer for years
and latency will always be determined by geographic region and equipment.
In my case I'm seeing less than 5 second latency, usually lower than 2, and
yes, of course you can run queries on the data that is being replicated.
Resources are minimal once replication is setup, during the initial
snapshot, the only issues you might run into are the locking of the tables
as they are processed for replication.
Adam P. Cassidy
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:D4AA35E2-CBA7-4052-93AB-37DF213D729D@.microsoft.com...[vbcol=seagreen]
> Paul, thanks a lot!
> Do you know any limitation on using transactional replication? eg the min
> delay time, can it perform query during the replication.
> Aslo how's the overhead on resources of compare to mirroring? Is it
> require
> many resources (eg. CPU and RAM) during the processing?
> Regards,
> Ryan
>
> "Paul Ibison" wrote:
|||Ryan,
I agree with Adam, but just to clarify, if you mean queries applied to the
publisher then there' s no issue, but if the query is to the subscriber, you
might experience the normal blocking issues. The new snapshot isolation
level can be of use here. I have no stats regarding the performance
comparison between database mirroring and replication as yet.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Yes agreed. Sorry for not clarifying. We replicate for Cognos reporting
and since non of the reports are require a committed state, we have all the
queries executed against the replicated data as read uncommitted and there
are no problems - definitely a point I should have made.
Adam
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OhCAQbWFHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Ryan,
> I agree with Adam, but just to clarify, if you mean queries applied to the
> publisher then there' s no issue, but if the query is to the subscriber,
> you might experience the normal blocking issues. The new snapshot
> isolation level can be of use here. I have no stats regarding the
> performance comparison between database mirroring and replication as yet.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Database Replication

We currently have multiple tables being replicated using merge snapshot. In
the majority of cases the whole table is required or a row filter is applied
based on a value in that table.
We have two sets of identical databases, except for their data, that
replicate their data from a backend database towards a frontend database.
The problem is that for database 1, all the necesarry data is replicated,
but for the other database 2, which has the same parameters, replication
settings etc.. certain blob files containing pdf files will not replicate
from the backend database towards the frontend database.
The replication only replicates the .doc files, so the replication is
partially done for the table that contains the .doc and .pdf files.
When I run the initial snapshot, the pdf files are replicated fine, but when
the agent runs, the pdf files are not replicated.
Has anyone encountered similar problems? Can someone identify what is
causing the problem by explaining why this is not working? My first idea
would be the sheduling the snapshot to run every night, instead of the
agent, but this would be a far from ideal solution and one that I would only
consider as a last choice. I fail to understand why the replication works
for database 1, but not for database 2.
parnold8104@.hotmail.com
can you run this command sp_configure 'max text repl size'
and then figure out what your maximum data size in the blob columns you are
replicating?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> We currently have multiple tables being replicated using merge snapshot.
In
> the majority of cases the whole table is required or a row filter is
applied
> based on a value in that table.
> We have two sets of identical databases, except for their data, that
> replicate their data from a backend database towards a frontend database.
> The problem is that for database 1, all the necesarry data is replicated,
> but for the other database 2, which has the same parameters, replication
> settings etc.. certain blob files containing pdf files will not replicate
> from the backend database towards the frontend database.
> The replication only replicates the .doc files, so the replication is
> partially done for the table that contains the .doc and .pdf files.
> When I run the initial snapshot, the pdf files are replicated fine, but
when
> the agent runs, the pdf files are not replicated.
> Has anyone encountered similar problems? Can someone identify what is
> causing the problem by explaining why this is not working? My first idea
> would be the sheduling the snapshot to run every night, instead of the
> agent, but this would be a far from ideal solution and one that I would
only
> consider as a last choice. I fail to understand why the replication works
> for database 1, but not for database 2.
> parnold8104@.hotmail.com
>
>
|||Hi Hilary,
thanks for replying, when I run the command sp_configure I get these values:
max text repl size (B) minimum: 0
maximum: 2147483647
config_value: 52428800
run_value: 52428800
I am not sure if the data size is the problem, because the .doc files that
are replicated are larger in size then the pdf files. Another thing I
noticed is that I can replicate my pdf records in the 'faulty' database, but
only if I do an update of the record in my backend database. So when the
record is 'new' the replication skips it, but if I update the record, by for
example changing a letter in a description field, the replication picks up
the record and pushes it towards the frontend database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> can you run this command sp_configure 'max text repl size'
> and then figure out what your maximum data size in the blob columns you
are[vbcol=seagreen]
> replicating?
> "Peter A" <parnold8104@.hotmail.com> wrote in message
> news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In
> applied
database.[vbcol=seagreen]
replicated,[vbcol=seagreen]
replicate[vbcol=seagreen]
> when
> only
works
>
|||can you post your schema and publication scripts here?
What version of SQL are you using? SP?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23whCdr3JEHA.232@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for replying, when I run the command sp_configure I get these
values:
> max text repl size (B) minimum: 0
> maximum: 2147483647
> config_value: 52428800
> run_value: 52428800
> I am not sure if the data size is the problem, because the .doc files that
> are replicated are larger in size then the pdf files. Another thing I
> noticed is that I can replicate my pdf records in the 'faulty' database,
but
> only if I do an update of the record in my backend database. So when the
> record is 'new' the replication skips it, but if I update the record, by
for[vbcol=seagreen]
> example changing a letter in a description field, the replication picks up
> the record and pushes it towards the frontend database.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> are
snapshot.[vbcol=seagreen]
> database.
> replicated,
replication[vbcol=seagreen]
> replicate
but[vbcol=seagreen]
idea[vbcol=seagreen]
would
> works
>

Database Replication

I have to create a replication for a database thar have 7 Gb. The time
that consume is very important: almost 7 hs. Exist a procedure more
efficient?
How can I generate a replication restoring a backup, instead of make an
snapshop (like Informix).
Thanks in advance
In addition to what Paul said: when I generate my snapshots I use the
"Compress the snapshot..." option.
These snapshots are considerably smaller than a normal backup.
Jim.
"cbaffigi@.apsf.com.ar" wrote:

> I have to create a replication for a database thar have 7 Gb. The time
> that consume is very important: almost 7 hs. Exist a procedure more
> efficient?
> How can I generate a replication restoring a backup, instead of make an
> snapshop (like Informix).
>
> Thanks in advance
>
|||Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
should compress to <2GB but as the size increases you might have to consider
an alternative - WinZip or WinRar etc.
Rgds,
Paul Ibison
|||are you sure you aren't thinking of the msf files involved in subscription
copy? There was a 2 Gig limit on zip files some time ago, but I believe this
has changed now.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> Also...from what I recall, the CAB file has a maximum size of 2GB. 7GB
> should compress to <2GB but as the size increases you might have to
consider
> an alternative - WinZip or WinRar etc.
> Rgds,
> Paul Ibison
>
|||I am afraid Paul is right with regard to the 2gig (overall compressed, per
uncompressed file) limitations in snapshot compression here. These
limitations partly came from our use of the Cabinet API and the fact that we
limit ourselves to a single cab file. The snapshot compression feature was
originally intended for folks trying to transfer a snapshot over (relatively
speaking) slow WAN and 2gig seemed plenty for that at the time. And if there
is a need to transfer a much larger amount of data, the recommendation is to
do what Paul described below.
I have pretty good idea of how we can retrofit the snapshot compression
feature to get around the 2gig limits and make it generally more pleasant to
use in terms of temporary storage requirements, but it is not easy to do and
will likely involve the use of a different set of compression apis other
than the cabinet API thereby causing backward compatibility breakages with
subscribers using existing version of replication binaries.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:efJEW139EHA.1452@.TK2MSFTNGP11.phx.gbl...
> are you sure you aren't thinking of the msf files involved in subscription
> copy? There was a 2 Gig limit on zip files some time ago, but I believe
this
> has changed now.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:O2t6qn19EHA.3640@.tk2msftngp13.phx.gbl...
> consider
>
|||Raymond,
hopefully you're still monitoring this thread and can answer a related
question for me. If the compression option is used in an alternative
snapshot location, and there isn't the default location selected, I was
trying to find out where is the cab file created during processing? The
directory seems to be immediately created, but there is no CAB file there
during the initial processing and outputting of data - is it all done in
memory, or is there a temp file held in another path? I ask because on some
servers we have severe space issues and I have had to change the WinZip temp
directory several times because of a similar issue.
Rgds,
Paul Ibison
|||Hi Paul,
By default, the cabinet api will build the cab file in the temp directory
and will only copy the resulting cab to the "real" location at the end. This
should explain the disk space problems that you were seeing. Knowing what I
know now, I probably would have implemented the snapshot compression feature
differently but then again, we really didn't (and still don't) have time to
do it properly.
-Raymond
This posting is provided "as is" with no warranties and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23I2NrZB%23EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Raymond,
> hopefully you're still monitoring this thread and can answer a related
> question for me. If the compression option is used in an alternative
> snapshot location, and there isn't the default location selected, I was
> trying to find out where is the cab file created during processing? The
> directory seems to be immediately created, but there is no CAB file there
> during the initial processing and outputting of data - is it all done in
> memory, or is there a temp file held in another path? I ask because on
some
> servers we have severe space issues and I have had to change the WinZip
temp
> directory several times because of a similar issue.
> Rgds,
> Paul Ibison
>
|||Thanks Raymond.
Rgds,
Paul

Database Replication

Jon,
for on-line pictures, ahve a look at
http://www.mssqlcity.com/Articles/Replic/Replic.htm
For a good informational guide, BOL is currently the best
resource, but Hilary Cotter is soon to release a series
of Replication books that would be useful to you
(http://www.nwsu.com/0974973602p.html).
HTH,
Paul Ibison
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote on Mon, 04 Oct 2004
12:08:56 GMT:

> for on-line pictures, ahve a look at
> http://www.mssqlcity.com/Articles/Replic/Replic.htm
Thank you to both you and Hilary, this is excatly what I was looking for.
Jono

Database Replication

Hello all,
I am looking for a "beginners guide" on setting up the automatic (daily)
replication of a production database to an identical database on another
server. This second database is to be used for training purposes. This is
for MSSQL 2000.
I have a customer that wishes to do this, and rather than explain it all I
would rather point him to a guide that he can look at (pleanty of pictures
would be good) and then ask questions if necessary.
Does anyone know of such a thing that is accessible?
Thank you in advance.
Jon Hunt
IT Manager
You might want to direct your customer to this -
http://www.mssqlcity.com/Articles/Re...TR/SetupTR.htm
There is some nonsense in here, but its ok. This is for SQL 7, but it is
much the same for SQL 2000.
"Jon Hunt" <thisisnotmyaddress@.nospam.invalid> wrote in message
news:Xns9578844C8F373softworks@.158.152.254.254...
> Hello all,
> I am looking for a "beginners guide" on setting up the automatic (daily)
> replication of a production database to an identical database on another
> server. This second database is to be used for training purposes. This is
> for MSSQL 2000.
> I have a customer that wishes to do this, and rather than explain it all I
> would rather point him to a guide that he can look at (pleanty of pictures
> would be good) and then ask questions if necessary.
> Does anyone know of such a thing that is accessible?
> Thank you in advance.
> --
> Jon Hunt
> IT Manager

Database Replication

Hi everybody

Can I replicate a database onto a seperate instance of SQL? I need to
replicate a database to a stand-alone machine with no connectivety to
anything. Does anyone know where I can find info on this?

Thanks for any help.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Perhaps in Books Online, under "Replication"? :-) And I guess you
don't really mean "no connectivity to anything", otherwise it would be
somewhat difficult to implement. Depending on what your goal is
(availability, disaster recovery, offline reporting etc.) you could
also look at log shipping, clustering, and of course backup/restore.

Replication is quite a specialized area, so if you have specific
questions about it, you'll probably get a better response in
microsoft.public.sqlserver.replication.

Simon|||Thanks Simon. I did check the Books Online ;-). All I need is our online
database on another stand-alone computer
that is not connected to any network (due to stupid security). I need this
for a whole bunch of testing on one of our apps. I think that the
backup/restore option would be better?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1|||If you have no network connection, you'll need to copy the database to
a physical medium (DVD, tape, USB drive) anyway, so backup/restore
would be the only real option (you could detach/attach, but that would
mean taking the source offline).

Also see this article:

http://support.microsoft.com/defaul...kb;en-us;314546

Simon

Database Replication

When I create a suscription with the option "No, the Suscriber already
has the schema and data", and I modify data, I have an error like
this:

Could not find stored procedure 'sp_MSdel_ac_callejero'.
(Source: SE110556 (Data source); Error number: 2812)

How can I generate a replication restoring a backup, instead of make an
snapshop.

Thanks in advance<cbaffigi@.apsf.com.ar> wrote in message
news:1105025754.482942.154890@.f14g2000cwb.googlegr oups.com...
> When I create a suscription with the option "No, the Suscriber already
> has the schema and data", and I modify data, I have an error like
> this:
> Could not find stored procedure 'sp_MSdel_ac_callejero'.
> (Source: SE110556 (Data source); Error number: 2812)
> How can I generate a replication restoring a backup, instead of make an
> snapshop.
> Thanks in advance

You might want to post this in microsoft.public.sqlserver.replication to see
if you get a better response there.

Simon

Database Replication

Hi,
I'm learning Database Replication. what's the Database Replication for? what
applications use this feature? Please advice.Hi
Have you read the section "Introducing Replication" in Books Online?
Hillary Cotter has a book on replication
http://www.nwsu.com/0974973602.html which may help if you proceed along
this route.
John
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||For having the same (or nearly the same) data in different places.
The most obvious example is a salesperson wanting to have a subset of the
the sales database on his laptop with the ability to make changes locally
and then have those changes get synchronized into the corporate database
when he re-connects to the LAN.
Also frequently used in Reporting Server situations, where the main
transactional database is too busy to serve up large queries for reports.
One way replication ships the data to a db that the reports are run off.
HTH,
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:OkY%233g0TFHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm learning Database Replication. what's the Database Replication for?
> what applications use this feature? Please advice.
>|||Thanks all...|||"Kevin3NF" wrote in message
> For having the same (or nearly the same) data in different places.
> The most obvious example is a salesperson wanting to have a subset of the
> the sales database on his laptop with the ability to make changes locally
> and then have those changes get synchronized into the corporate database
> when he re-connects to the LAN.
> Also frequently used in Reporting Server situations, where the main
> transactional database is too busy to serve up large queries for reports.
> One way replication ships the data to a db that the reports are run off.
>
Thanks Kevin, There are good sample, easy to understand.
How about Analysis Services? Can you please give me some samples too.|||When the CEO wants to know how many widgets were sold in the NorthEast
district in Q4, 2004...he's going to open up Excel, connect to the Analysis
Services database and select those parameters. Very easy and he doesn't
have to know any programming.
Lots of work goes into making that AS database, form bringing the data in,
to getting it ready for the boss to start slicing and dicing.
I've never played in that area, so my knowledge is limited. I am pitching a
project that involves this, so ping me in early 2006...I should be much more
well-versed by then :-)
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"js" <js@.someone@.hotmail.com> wrote in message
news:uqiB5q1TFHA.3584@.TK2MSFTNGP14.phx.gbl...
> "Kevin3NF" wrote in message
>> For having the same (or nearly the same) data in different places.
>> The most obvious example is a salesperson wanting to have a subset of the
>> the sales database on his laptop with the ability to make changes locally
>> and then have those changes get synchronized into the corporate database
>> when he re-connects to the LAN.
>> Also frequently used in Reporting Server situations, where the main
>> transactional database is too busy to serve up large queries for reports.
>> One way replication ships the data to a db that the reports are run off.
> Thanks Kevin, There are good sample, easy to understand.
> How about Analysis Services? Can you please give me some samples too.
>
>|||Thanks Kevin...

database repair without data loss?

Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.

Database reorganization

Database reorganization
We use SQL 2000 and our application database is around 2 Gb.
A third party consultant (Microsoft Certified) counseled us to perform
database reorganization, at least once each year.
The way he recommend us to execute the database reorganization is the
following:
1) backup the app database
2) delete the app database
3) restore the app database with another name oldDB
4) create a new app database
5) export all objects and data from oldDB to app DB by DTS
The questions I would like to ask you are:
Is that really necessary?
Is that really effective?
Is there another way to perform database reorganization?
Thanks,
MarcosWhy? Looks very strange.
You should have proper DataBase Disaster Recovery Strategy , means backup
all user databases as well as system databases and many many other things
<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152100999.019653.291350@.a14g2000cwb.googlegroups.com...
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Not sure why you would go through all that. Personally I would never delete
my database to restore it back again. What happens if your restore does not
work? If you really wanted to do that I would do 2 and 3 the other way round
that way you have not lost anything if it does not go to plan.
My database is 288GB and I run a reorg job on it a few times a year
(although I am planning to run a regular job), by using one of the
maintenance plans. I would suggest you take a look at these before you start
deleting databases. Having said all that though unless your database is
growing rapidly you will probably see no noticable improvement, mine is
growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
after this database it was 150GB and it had never had a reorg job run on it,
performance was fine, but when I first ran it there was a very noticable
difference. I'm no SQL guru but this way works for me and it lets me sleep
easy, when we do a restore I''m usually at work all night.
Gav|||mripplinger74@.yahoo.com.br wrote:
> Database reorganization
> We use SQL 2000 and our application database is around 2 Gb.
> A third party consultant (Microsoft Certified) counseled us to perform
> database reorganization, at least once each year.
> The way he recommend us to execute the database reorganization is the
> following:
> 1) backup the app database
> 2) delete the app database
> 3) restore the app database with another name oldDB
> 4) create a new app database
> 5) export all objects and data from oldDB to app DB by DTS
> The questions I would like to ask you are:
> Is that really necessary?
> Is that really effective?
> Is there another way to perform database reorganization?
>
> Thanks,
> Marcos
>
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--060703000702060505050207
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
Tracy McKibben wrote:
> mripplinger74@.yahoo.com.br wrote:
>> Database reorganization
>> We use SQL 2000 and our application database is around 2 Gb.
>> A third party consultant (Microsoft Certified) counseled us to perform
>> database reorganization, at least once each year.
>> The way he recommend us to execute the database reorganization is the
>> following:
>> 1) backup the app database
>> 2) delete the app database
>> 3) restore the app database with another name oldDB
>> 4) create a new app database
>> 5) export all objects and data from oldDB to app DB by DTS
>> The questions I would like to ask you are:
>> Is that really necessary?
>> Is that really effective?
>> Is there another way to perform database reorganization?
>>
>> Thanks,
>> Marcos
> Gosh, I'm also Microsoft certified, and I'm going to tell you that
> consultant is all wet. Guess you have to wonder how useful those
> certifications are, huh?
> A 2GB database is NOTHING - I've been working with 100+GB database for
> several years. There is no need to do any "reorganization". Keep
> your indexes defragged, keep the database files properly sized, don't
> shrink your database or log files, and be vigilant about making sure
> the queries hitting your database are written properly and efficiently.
>
..well...nobody said that this consultant was certified on anything
related to SQL server... ;-)
Regards
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...)
Databaseadministrator / Systemadministrator
--060703000702060505050207
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tracy McKibben wrote:
<blockquote cite="miduJIjCXGoGHA.4432@.TK2MSFTNGP05.phx.gbl" type="cite"><a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:mripplinger74@.yahoo.com.br">mripplinger74@.yahoo.com.br</a>
wrote:
 
<blockquote type="cite">Database reorganization
 
 
We use SQL 2000 and our application database is around 2 Gb.
 
 
A third party consultant (Microsoft Certified) counseled us to perform
 
database reorganization, at least once each year.
 
 
The way he recommend us to execute the database reorganization is the
 
following:
 
 
1) backup the app database
 
2) delete the app database
 
3) restore the app database with another name oldDB
 
4) create a new app database
 
5) export all objects and data from oldDB to app DB by DTS
 
 
The questions I would like to ask you are:
 
 
Is that really necessary?
 
 
Is that really effective?
 
 
Is there another way to perform database reorganization?
 
 
 
Thanks,
 
 
Marcos
 
 
</blockquote>
 
Gosh, I'm also Microsoft certified, and I'm going to tell you that
consultant is all wet. Guess you have to wonder how useful those
certifications are, huh?
 
 
A 2GB database is NOTHING - I've been working with 100+GB database for
several years. There is no need to do any "reorganization". Keep your
indexes defragged, keep the database files properly sized, don't shrink
your database or log files, and be vigilant about making sure the
queries hitting your database are written properly and efficiently.
 
 
 
</blockquote>
..well...nobody said that this
consultant was certified on anything related to SQL server... ;-) 
 
 
-- 
Regards 
Steen Schlüter Persson (...who isn't certified in anything but just
knows something about SQL server...) 
Databaseadministrator / Systemadministrator 

</body>
</html>
--060703000702060505050207--|||Uri.
We have a Disaster Recovery Plan but my question here is how to
reorganize database trying to improve his performance.
Thanks,
Marcos
Uri Dimant escreveu:
> Why? Looks very strange.
> You should have proper DataBase Disaster Recovery Strategy , means backup
> all user databases as well as system databases and many many other things
>|||Gav,
Just to be sure, when you say "reorg job" you are talking about use
a maintenance plan with the option reorganize data and index page
selected, right?
Do you believe it is enough? Even in the tables where the PK is not a
clustered index?
Thanks,
Marcos
Gav escreveu:
> Not sure why you would go through all that. Personally I would never delete
> my database to restore it back again. What happens if your restore does not
> work? If you really wanted to do that I would do 2 and 3 the other way round
> that way you have not lost anything if it does not go to plan.
> My database is 288GB and I run a reorg job on it a few times a year
> (although I am planning to run a regular job), by using one of the
> maintenance plans. I would suggest you take a look at these before you start
> deleting databases. Having said all that though unless your database is
> growing rapidly you will probably see no noticable improvement, mine is
> growing at a rate of 1.5 to 2GB a week. Incidentally when I started looking
> after this database it was 150GB and it had never had a reorg job run on it,
> performance was fine, but when I first ran it there was a very noticable
> difference. I'm no SQL guru but this way works for me and it lets me sleep
> easy, when we do a restore I''m usually at work all night.
> Gav|||<mripplinger74@.yahoo.com.br> wrote in message
news:1152202785.006599.122210@.75g2000cwc.googlegroups.com...
> Gav,
> Just to be sure, when you say "reorg job" you are talking about use
> a maintenance plan with the option reorganize data and index page
> selected, right?
> Do you believe it is enough? Even in the tables where the PK is not a
> clustered index?
>
> Thanks,
> Marcos
Yes thats what I am talking about. My database contains approx 38,000
tables, the majority of these have clustered indexes. I have no idea how
many don't but the ones I have come accross so far are fairly insignificant
anyway. If you know of some tables without clustered indexes in your
database I would run a DBCC SHOWCONTIG before and after running a reorg job
and see what the effect is.
I plot database stats weekly and monthly, when the responce times start to
climb I run a reorg job and the responce times fall again. So from my point
of view it works fine.
Gav|||Tracy,
I agree that our database is not big, but neither our servers are. In
someplace the database is running in a workstation or in a laptop,
that's why I do care about it.
We have a maintenance plan to reorganize data and index page and we
schedule it to run every weekend, but I questioned if is it enough?
What the procedure detailed above give me more than this maintenance
plan?
Regards,
Marcos|||Sorry, I forgot to mention the guy is MCDBA :D|||mripplinger74@.yahoo.com.br wrote:
> Tracy,
> I agree that our database is not big, but neither our servers are. In
> someplace the database is running in a workstation or in a laptop,
> that's why I do care about it.
> We have a maintenance plan to reorganize data and index page and we
> schedule it to run every weekend, but I questioned if is it enough?
> What the procedure detailed above give me more than this maintenance
> plan?
>
> Regards,
> Marcos
>
There are basically four things that will cause a database to "slow down":
1. Bad queries - poorly written code, cursors, lack of supporting
indexes, these things are by far the most common reason for a database
to suddenly slow down.
2. Index fragmentation - as indexes grow due to new data being added to
tables, they suffer from page splits. These page splits lead to
fragmented indexes, which hinder performance because the query engine
has to jump around looking for the next index page. DBCC DBREINDEX will
rebuild those indexes, putting them back together into contiguous blocks.
3. Disk fragmentation - repeated shrinking/growing of a database or
transaction log file will cause it to become fragmented on disk. Just
as index fragmentation hurts performance, so do physical fragmentation.
This can be prevented by properly sizing the data files so that they
don't need to grow often, and NEVER shrink them.
4. Outdated statistics - when decided what indexes to use, the query
engine looks at database statistics to get a sampling of how data is
distributed in the tables. If these stats are not current, then indexes
can't be fully utilized, leading to table or index scans, which are the
slowest form of data access. Generally, turning on the auto-stats
options on your databases will be enough to prevent this, but
inserting/deleting large amounts of data may require manually updating
the stats.
That's it. If you make it a point to stay on top of those four things,
you should not see a performance degradation. You can easily manage
these without a maintenance plan. There is nothing in those wizards
that will make up for insufficient hardware, they simply provide a
point-and-click front-end for managing the 4 tasks above (also backups
and CHECKDB). Learn how to manage these things without the aid of the
wizards, you'll learn alot, and be better equipped to deal with problems
when they do arise.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On Fri, 07 Jul 2006 08:26:25 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>There are basically four things that will cause a database to "slow down":
2gb database might "speed up" if you run it on machines with at least
that much RAM, which maybe you (OP) haven't been doing?
Josh

Database Renaming

Can anyone suggest a quick and easy way to rename a database AND .MDF/.LDF file in SQL Server?
I can not, for the life of me, figure out a way to do this.
Help?You could try to backup database and restore it with another name like this (before restoring you may delete old database).

Old db name is rDB, new will be rrDB.

RESTORE DATABASE rrDB
FROM DISK = 'w:\rdb.bak'
WITH MOVE 'rDB_Data' TO 'w:\rrDB.mdf',
MOVE 'rDB_Log' TO 'w:\rrDB.ldf'|||Originally posted by JBoyce
Can anyone suggest a quick and easy way to rename a database AND .MDF/.LDF file in SQL Server?
I can not, for the life of me, figure out a way to do this.
Help?
You can also use sp_renamedb|||Originally posted by smasanam
You can also use sp_renamedb

But file names will be the same...|||"Alter database" has a way to rename logical files, but the physical files will remain the same. If the DB name and the filenames all have to change, then Snail has the right way to go.

database rename

Hi just wondering how to rename the database using enterprise manager. thanks.
Paul G
Software engineer.
I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:

> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.
|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:

> Hi just wondering how to rename the database using enterprise manager. thanks.
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner
|||ok thanks for the replies.
Paul G
Software engineer.
"Rouven Hausner" wrote:

> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
>
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>
|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.
|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>

database rename

Hi just wondering how to rename the database using enterprise manager. thank
s.
--
Paul G
Software engineer.I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:

> Hi just wondering how to rename the database using enterprise manager. tha
nks.
> --
> Paul G
> Software engineer.|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
[vbcol=seagreen]
> Hi just wondering how to rename the database using enterprise manager. thanks.[/vb
col]
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner|||ok thanks for the replies.
--
Paul G
Software engineer.
"Rouven Hausner" wrote:

> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
>
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. tha
nks.
> --
> Paul G
> Software engineer.|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>

database rename

Hi just wondering how to rename the database using enterprise manager. thanks.
--
Paul G
Software engineer.I'd use Query Analyzer:
1. Get everyone out of the database.
2. Look up sp_rename_db
"Paul" wrote:
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.|||Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
> Hi just wondering how to rename the database using enterprise manager. thanks.
Hi, you have to use the QueryAnalyzer with sp_rename.
Greetings
Rouven Hausner|||ok thanks for the replies.
--
Paul G
Software engineer.
"Rouven Hausner" wrote:
> Am Fri, 4 Nov 2005 14:53:03 -0800 schrieb Paul:
> > Hi just wondering how to rename the database using enterprise manager. thanks.
> Hi, you have to use the QueryAnalyzer with sp_rename.
> Greetings
> Rouven Hausner
>|||As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
> Hi just wondering how to rename the database using enterprise manager. thanks.
> --
> Paul G
> Software engineer.|||Hi,
You can use either ALTER DATABASE or sp_renamedb to rename a database.
Eg:-
EXEC sp_renamedb 'accounting', 'financial'
or
ALTER DATABASE Accounting MODIFY NAME = financial
Thanks
Hari
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:enZ3lDq4FHA.3352@.TK2MSFTNGP10.phx.gbl...
> As of SQL Server 2000, you can use ALTER DATABASE to rename a database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4D3DC728-CDF6-40D5-BECA-C3E01AA0EAC4@.microsoft.com...
>> Hi just wondering how to rename the database using enterprise manager.
>> thanks.
>> --
>> Paul G
>> Software engineer.
>

Database remove/delete

How can I remove/delete a database.

I try (below) but not work:

Dim wServer As New Server(strSqlName)

wServer.Databases("DataBaseName").Drop()

Thanks,

Try defining a Database object first, as in this sample:

Dim srv1 As Server
srv1 = New Server("MyServer")
'Define a Database object variable
Dim db1 As Database
'Reference the database
db1 = srv1.Databases("Test_Database")
Console.WriteLine(db1.CreateDate)
'Remove the database.
db1.Drop()

I've tested this and it works.

|||

Thanks Allen.

Very good.

I am tested too and it works very well.

Marsenne

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation ?
Thanks in advance.
I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:

> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation ?
> Thanks in advance.
>
>
|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribi en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a[vbcol=seagreen]
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation '
Thanks in advance.I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:

> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation '
> Thanks in advance.
>
>|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribi en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a[vbcol=seagreen]
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:
>

Database removable

Hello,is possible attach and use the mdf exists in a pen drive or an usb
hard disk ?
What commands are involved in this operation '
Thanks in advance.I have a lot of DB allocated on my USB (or Firewire) external disk; even a
pen drive is managed by the OS as an external, removable disk.
The only attention you hav to pay is to remember detach you removable
database when you suppose the next startup of your PC will be without the
external disks containing your databases.
When you need to use such databases you'll attach it again
Gilberto
"Luis Tarzia" wrote:
> Hello,is possible attach and use the mdf exists in a pen drive or an usb
> hard disk ?
> What commands are involved in this operation '
> Thanks in advance.
>
>|||I attach the mdf with the sp_attachd but when i execute any sql command the
sql mark an error of access a memory and exit.
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> escribió en
el mensaje news:5BF5A51B-83A5-4A23-B540-B104BBCDD7AC@.microsoft.com...
> I have a lot of DB allocated on my USB (or Firewire) external disk; even
a
> pen drive is managed by the OS as an external, removable disk.
> The only attention you hav to pay is to remember detach you removable
> database when you suppose the next startup of your PC will be without the
> external disks containing your databases.
> When you need to use such databases you'll attach it again
> Gilberto
> "Luis Tarzia" wrote:
> > Hello,is possible attach and use the mdf exists in a pen drive or an usb
> > hard disk ?
> > What commands are involved in this operation '
> > Thanks in advance.
> >
> >
> >

Database Releases

I'm looking for documentation templates to specify releases or promotions of
items from Dev to QA to Prod. Has anyone got any recommendations or
references to some that already exist?
Thanks - Craig.Hi
If you have a bug tracking system and version control this can be done
process that combines labelling fixes and extracting the appropriate version
from the repository. Exactly how depends on what systems you are using. There
may be some white papers on the web sites for these sort of products such as
Serena (formally Merant PVCS).
John
"Craig" wrote:
> I'm looking for documentation templates to specify releases or promotions of
> items from Dev to QA to Prod. Has anyone got any recommendations or
> references to some that already exist?
> Thanks - Craig.|||Thanks for the reply. I was hoping to locate a MS Word template that might
aid in this effort.
- Craig
"John Bell" wrote:
> Hi
> If you have a bug tracking system and version control this can be done
> process that combines labelling fixes and extracting the appropriate version
> from the repository. Exactly how depends on what systems you are using. There
> may be some white papers on the web sites for these sort of products such as
> Serena (formally Merant PVCS).
> John
>
> "Craig" wrote:
> > I'm looking for documentation templates to specify releases or promotions of
> > items from Dev to QA to Prod. Has anyone got any recommendations or
> > references to some that already exist?
> >
> > Thanks - Craig.|||Hi
Sorry I didn't read "template"!
If you are looking for a process then it will depend on the systems and
configurations that you are using, therefore I would not really expect it to
fit a generic template.
I would assume that you already have some form of process that you can then
reverse engineer and then formalise/improve?
John
"Craig" wrote:
> Thanks for the reply. I was hoping to locate a MS Word template that might
> aid in this effort.
> - Craig
>
> "John Bell" wrote:
> > Hi
> >
> > If you have a bug tracking system and version control this can be done
> > process that combines labelling fixes and extracting the appropriate version
> > from the repository. Exactly how depends on what systems you are using. There
> > may be some white papers on the web sites for these sort of products such as
> > Serena (formally Merant PVCS).
> >
> > John
> >
> >
> > "Craig" wrote:
> >
> > > I'm looking for documentation templates to specify releases or promotions of
> > > items from Dev to QA to Prod. Has anyone got any recommendations or
> > > references to some that already exist?
> > >
> > > Thanks - Craig.

Database Releases

Database Relationships

Hi,

Im managing our companys database which has approx 250 tables in it.
It is a design i have inherited from the people who originally created it, and i'm just having a look at all the foreign key relationships in it.

The actually have not defined many of these relationships. I'm just working through it now and placing these in now.

My question is this: Being such a complex database, can you have too many relationship?
For example, columns 'product_ID','Customer_ID', 'Branch_ID', and 'User_ID' occur in around 50 tables each.

If I enforce all these relationships, is it going to have a negative impact on the database? Is there any other factors i should consider? Is there a limit to how many relationships you should enforce?

Any help would be appreciated.

Josh DolanIMO it would really depend on who is access the database and how...

Sometime enforcing foreign key contraints is going to cause more problems then they are going to solve... but it should be evaluated on a case per case basis...|||I don't have any problem with enforcing all the constraints i want to implement. I would rather deal with the problems that it may occur and have them implemented than nothing at all.

I'm just thinking from the performance and database design angle.
Is it going to degrade performance that much? And, it is good database design to enforce most if not all foreign keys?

Thanks,

Josh|||Providing the foriegn key contraints are valid then IMO you can do no harm in implementing them providing you are happy to deal with possible issues.

Performace may be affected but not to any real extent IMO.|||You don't have too many relationships. They are necessary to maintain the relational integrity of your data, and if you remove them and allow inconsistencies to develop in your data then the extra coding and admin you will need to do to account for the errors WILL slow down your system.

It is more likely that you have too many tables, not too many relationships. Unfortunately, if there is already an interface and/or reporting structure developed around this schema, you are probably stuck with it.

The next time your company is considering developing an important database, advise them to hire a professional database designer to help them. A database is as important to an application as a foundation is important to a house. When I had my home built, I didn't go buy a book at Home Depot and then start pouring concrete myself. I hired professionals.

blindman

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
Betre
Two ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre
|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:[vbcol=seagreen]
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
BetreTwo ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:[vbcol=seagreen]
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table
.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...

Database Relationship

Hi all,
Where can I see the Table relationship in Enterprise Manager. I see all
Tables but to run some query using two or more tables I need to know which
table is connected with that particular table. Can someone help me.
Thanks,
BetreTwo ways, and they only work if there is actually a defined relationship.
1. Look in the diagrams, or create a new one with the tables in question.
Anything you do here as far as adding/deleting may affect the actual table.
Its not just a picture...
2. Create a new view and add the tables. If the relationship has been
defined, it will fill in automatically
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"betrek" <betrek@.discussions.microsoft.com> wrote in message
news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> Hi all,
> Where can I see the Table relationship in Enterprise Manager. I see all
> Tables but to run some query using two or more tables I need to know which
> table is connected with that particular table. Can someone help me.
> Thanks,
> Betre|||Also, you can right click on tables and look at the design. Then right
click on columns and check out relationships, keys, indexes, etc.
Kevin3NF wrote:
> Two ways, and they only work if there is actually a defined relationship.
> 1. Look in the diagrams, or create a new one with the tables in question.
> Anything you do here as far as adding/deleting may affect the actual table.
> Its not just a picture...
> 2. Create a new view and add the tables. If the relationship has been
> defined, it will fill in automatically
>
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
>
> "betrek" <betrek@.discussions.microsoft.com> wrote in message
> news:1F84DE39-7EC6-4DA7-937B-0F8D8EDC90E9@.microsoft.com...
> > Hi all,
> >
> > Where can I see the Table relationship in Enterprise Manager. I see all
> > Tables but to run some query using two or more tables I need to know which
> > table is connected with that particular table. Can someone help me.
> >
> > Thanks,
> >
> > Betre

Database reindex

Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks
Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:

> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i ca
n
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:

> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don'
t
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separa
te
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want t
o
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:

> Sunday it ran this was the normal day i had it set all the other days mond
ay
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i ge
t
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor an
d
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fil
l
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Saturday, February 25, 2012

Database Replication

database replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

Database Replication

database repair without data loss?

Database reorganization

Database reorganization

Database Renaming

database rename

database rename

database rename

Database remove/delete

Database removable

Database removable

Database removable

Database Releases

Database Releases

Database Releases

Database Relationships

Database Relationship

Database Relationship

Database Relationship

Database reindex

Database reindex

Database Unit Testing

Blog Archive

About Me