interface supported: hii

Showing posts with label hii. Show all posts

Friday, March 30, 2012

NOSKIP & SKIP make no difference to the amount of time backup take

Hi
I run a full backup once a month and a transaction log backup every 10 mins
through working hours for the remainder of the month until the backup is
re-initialised at th start of the following month.
I backup to virtual sql disk object devices.
As the month progresses the backup takes longer and longer whilst the amount
of data being backed up every 10 minutes cycle is always roughly the same.
its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
month.
I noticed that NOSKIP was being used in the command - i changed this to
SKIP, but it made no difference in the amount of time the backup takes. Isnt
this suppoed to stop some sort of integrity scan on all other backup sets in
the archive?
full syntax of the backup command that runs now is:
BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
but as I say, the SKIP command instead of NOSKIP makes no difference.
whats going on here?
any help appreciated.
Thanks
Alastair.
On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
wrote:
> Hi
> I run a full backup once a month and a transaction log backup every 10 mins
> through working hours for the remainder of the month until the backup is
> re-initialised at th start of the following month.
> I backup to virtual sql disk object devices.
> As the month progresses the backup takes longer and longer whilst the amount
> of data being backed up every 10 minutes cycle is always roughly the same.
> its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
> month.
> I noticed that NOSKIP was being used in the command - i changed this to
> SKIP, but it made no difference in the amount of time the backup takes. Isnt
> this suppoed to stop some sort of integrity scan on all other backup sets in
> the archive?
> full syntax of the backup command that runs now is:
> BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
> NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
> but as I say, the SKIP command instead of NOSKIP makes no difference.
> whats going on here?
> any help appreciated.
> Thanks
> Alastair.
Noskip/skip does not make any difference on Backup time. It just
checks expiration date of backup sets. I just guess that the reason
may be that your disk gets fragmented after number of backups and that
may cause delay.
|||yes but if theres a LOT of backup sets surely this will add a delay?
"amish" wrote:

> On Aug 1, 3:44 pm, Methodology <Methodol...@.discussions.microsoft.com>
> wrote:
> Noskip/skip does not make any difference on Backup time. It just
> checks expiration date of backup sets. I just guess that the reason
> may be that your disk gets fragmented after number of backups and that
> may cause delay.
>
|||Looking at the BACKUP commands you posted, which includes REWIND and
NOFORMAT, it looks like the backup goes to tape. When you referred to
"virtual sql disk object devices" I was not expecting that. Tapes are
not virtual disks, tapes are tapes.
I have seen that behavior when backing up directly to tape. The
problem in that case was reading the tape from the start to the point
where the prior backup ended. Lets consider that by the end of the
month there have been around 1000 log backups assuming a 5 day work
week and an 8 hour work day.
I suggest changing the REWIND option to NOREWIND. From the
documentation: "NOREWIND -- Specifies that SQL Server will keep the
tape open after the backup operation. You can use this option to help
improve performance when performing multiple backup operations to a
tape."
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:

>Hi
>I run a full backup once a month and a transaction log backup every 10 mins
>through working hours for the remainder of the month until the backup is
>re-initialised at th start of the following month.
>I backup to virtual sql disk object devices.
>As the month progresses the backup takes longer and longer whilst the amount
>of data being backed up every 10 minutes cycle is always roughly the same.
>its about 5 seconds for a backup on day 1, up to 5 mins at the end of the
>month.
>I noticed that NOSKIP was being used in the command - i changed this to
>SKIP, but it made no difference in the amount of time the backup takes. Isnt
>this suppoed to stop some sort of integrity scan on all other backup sets in
>the archive?
>full syntax of the backup command that runs now is:
>BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT ,
>NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10
>but as I say, the SKIP command instead of NOSKIP makes no difference.
>whats going on here?
>any help appreciated.
>Thanks
>Alastair.
|||thanks for your response Roy.
if you go to server objects >backup devices you can configure a virtual
'tape' that is in effect a file on the physical disk. this is what im backing
up to. as far as I can tell the only differnece between using this method and
backing up direct to disk is that you get to use a shorter name in the backup
job command rather than a full file path.
anyhoo
may be this ISNT the only differnce. REWIND is default right? so maybe SQL
is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
what happens in 2 backups time (20 mins.)
cheers for the pointer
"Roy Harvey" wrote:

> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
> <Methodology@.discussions.microsoft.com> wrote:
>
|||On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> Looking at the BACKUP commands you posted, which includes REWIND and
> NOFORMAT, it looks like the backup goes to tape. When you referred to
> "virtual sql disk object devices" I was not expecting that. Tapes are
> not virtual disks, tapes are tapes.
> I have seen that behavior when backing up directly to tape. The
> problem in that case was reading the tape from the start to the point
> where the prior backup ended. Lets consider that by the end of the
> month there have been around 1000 log backups assuming a 5 day work
> week and an 8 hour work day.
> I suggest changing the REWIND option to NOREWIND. From the
> documentation: "NOREWIND -- Specifies that SQL Server will keep the
> tape open after the backup operation. You can use this option to help
> improve performance when performing multiple backup operations to a
> tape."
> Roy Harvey
> Beacon Falls, CT
> On Wed, 1 Aug 2007 03:44:00 -0700, Methodology
>
> <Methodol...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> - Show quoted text -
I dont have much idea about tapes but in BOL it says that
NOREWIND implies NOUNLOAD, and these options are incompatible within a
single BACKUP statement
and Methodology has specified also NOUNLOAD in the script.
|||fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
implies the former
thanks
"amish" wrote:

> On Aug 1, 4:37 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> I dont have much idea about tapes but in BOL it says that
> NOREWIND implies NOUNLOAD, and these options are incompatible within a
> single BACKUP statement
> and Methodology has specified also NOUNLOAD in the script.
>
|||NOUNLOAD/UNLOAD and REWIND/NOREWIND are tape-specific commands. If
you are not using tape they should not be in the BACKUP command at
all.
However, I am paranoid when it comes to dealing with backups. I don't
know that anyone has ever confirmed that there are no side effects to
using tape-specific features with a disk backup. So while I would
suggest removing the tape-specific parameters if you are not using
tape, I would combine that with starting a fresh backup file. It
would not hurt to backup to another device or file for the rest of the
month.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:02:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>thanks for your response Roy.
>if you go to server objects >backup devices you can configure a virtual
>'tape' that is in effect a file on the physical disk. this is what im backing
>up to. as far as I can tell the only differnece between using this method and
>backing up direct to disk is that you get to use a shorter name in the backup
>job command rather than a full file path.
>anyhoo
>may be this ISNT the only differnce. REWIND is default right? so maybe SQL
>is 'rewinding' the virtual tape? ive changed the job to norewind and ill see
>what happens in 2 backups time (20 mins.)
>cheers for the pointer
>"Roy Harvey" wrote:
|||no difference - still taking 5 minutes...
heeeelllpppppp
"Methodology" wrote:
[vbcol=seagreen]
> fair point - ive taken out NOUNLOAD and changedto NOREWIND as the latter
> implies the former
> thanks
>
> "amish" wrote:
|||I guess the answer is not to write so many backups to a single file.
Personally I don't think I would set up a backup scheme with one
monthly database backup and log backups every ten minutes. If a
database is active enough to justify a ten-minute interval for the
logs, I would backup the enitre database at least weekly, but more
likely daily. Of course I have no way of knowing your environment and
specific factors that went into the design.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Aug 2007 05:40:02 -0700, Methodology
<Methodology@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>no difference - still taking 5 minutes...
>
>heeeelllpppppp
>
>
>"Methodology" wrote:

NOSKIP & SKIP make no difference to the amount of time backup take

Friday, March 23, 2012

Noob question regarding "Data Warehouse"

i'm a total noob with the term "data warehouse"

i have 1 db with 1 big table in it.

i was asked from a guy who has no experience either with data warehousing to build a "data warehouse table - no redundancy"

i haven't got a clue how to do it or does this sentence even has a sence to it.

lots of tools on sql server 2005 yet, i don't know where to go in order to move this thing forward.

help would be appreciated.

Avi. :)

Refer to http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx about DW operations guide on SQL Server 2000 which can give you more information, for SQL 2005 as of now you can depend upon books online in this case, also this is an question with open answers and unless you have specific need it is hard to pinpoint the solution.

noob alert! What i thought was a real simple query...

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber

Thanks for any suggestions you might have.
Jthis perhaps?
SELECT itemNumberSet.itemNumber
FROM itemNumberSet
LEFT OUTER
JOIN itemUPCtable
ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
ORDER
BY itemNumberSet.itemNumber|||I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

Thanks again
J|||you can go a long way with online free sql tutorials

however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

there are a couple of good sites listed here: http://r937.com/links.cfm?links=sqlsql

Tuesday, March 20, 2012

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra
> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
[vbcol=seagreen]
>--Original Message--
a "Nonclustered[vbcol=seagreen]
UNIQUE
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>
|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...[vbcol=seagreen]
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
> a "Nonclustered
> UNIQUE
> UNIQUE INDEX then it
> didn't explicitly create
> for you. Try it!
> unique index
> it is enforced like a
> using sp_help or
> index will be
|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
>
|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra

>--Original Message--
a "Nonclustered[vbcol=seagreen]
UNIQUE[vbcol=seagreen]
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...[vbcol=seagreen]
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>
> a "Nonclustered
> UNIQUE
> UNIQUE INDEX then it
> didn't explicitly create
> for you. Try it!
> unique index
> it is enforced like a
> using sp_help or
> index will be|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Monday, March 12, 2012

Non sharing data cluster

Hi:

I'm trying to setup a MS Cluster but I don't know if it is feasible to
configure it in the way I think.

I have two machines with win2k server and MSSQL-2000 one of them is
currently performing as the production database and the other one is
the backup. The secondary one is keeping updated via the "Log
Shipping" technic.

We almost covered all the other possible failures of the other
components (ie: network, power, application servers, etc), the data is
maintaned in a raid which is ok but we want to cover also the
possibility of that failure too (yes, you can call us paranoids!! ).

The thing is we want to create a cluster that do not share the data,
but each machine of the cluster have thei own set of the same data.

My intention is to configure the cluster to detect a failure of one
machine and initiate the failover to the remaing one using the
appropiate scripts related to the promotion of a secondary server
keeped updateusing "log shipping".

Have anyone some experience with that kind of solution ??

Thanks in advance

Leonardo"Leonardo C" <lcomunia@.hotmail.com> wrote in message
news:fcc135d.0406150953.6fecf39a@.posting.google.co m...
> Hi:
> I'm trying to setup a MS Cluster but I don't know if it is feasible to
> configure it in the way I think.
> I have two machines with win2k server and MSSQL-2000 one of them is
> currently performing as the production database and the other one is
> the backup. The secondary one is keeping updated via the "Log
> Shipping" technic.
> We almost covered all the other possible failures of the other
> components (ie: network, power, application servers, etc), the data is
> maintaned in a raid which is ok but we want to cover also the
> possibility of that failure too (yes, you can call us paranoids!! ).
> The thing is we want to create a cluster that do not share the data,
> but each machine of the cluster have thei own set of the same data.
> My intention is to configure the cluster to detect a failure of one
> machine and initiate the failover to the remaing one using the
> appropiate scripts related to the promotion of a secondary server
> keeped updateusing "log shipping".
> Have anyone some experience with that kind of solution ??
> Thanks in advance
> Leonardo

I don't believe that's possible with a true cluster - in a cluster, if one
node goes down, its resources are immediately taken over by another node. A
cluster is intended for when you need absolute minimum downtime, but if you
can take the time to apply log shipping scripts, then I guess for you some
downtime is acceptable. In that case, why not just continue to use log
shipping?

But I must admit that I've never used clustering myself, so you might want
to post in microsoft.public.sqlserver.clustering to see if you can get some
more specific comments.

Simon|||Hi Simon:

Thanks for your response. Yes a minimum downtime is acceptable but as Log
Shipping needs a manual intervention a cluster can be the piece to automate
that part.
I'm forwarding this post to the Microsoft groups too, I'm not getting the
other one mentioned by you.

Maybe somebody knows how to automate this, without needing a Cluster.

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40cf3cc6$1_3@.news.bluewin.ch...
> "Leonardo C" <lcomunia@.hotmail.com> wrote in message
> news:fcc135d.0406150953.6fecf39a@.posting.google.co m...
> > Hi:
> > I'm trying to setup a MS Cluster but I don't know if it is feasible to
> > configure it in the way I think.
> > I have two machines with win2k server and MSSQL-2000 one of them is
> > currently performing as the production database and the other one is
> > the backup. The secondary one is keeping updated via the "Log
> > Shipping" technic.
> > We almost covered all the other possible failures of the other
> > components (ie: network, power, application servers, etc), the data is
> > maintaned in a raid which is ok but we want to cover also the
> > possibility of that failure too (yes, you can call us paranoids!! ).
> > The thing is we want to create a cluster that do not share the data,
> > but each machine of the cluster have thei own set of the same data.
> > My intention is to configure the cluster to detect a failure of one
> > machine and initiate the failover to the remaing one using the
> > appropiate scripts related to the promotion of a secondary server
> > keeped updateusing "log shipping".
> > Have anyone some experience with that kind of solution ??
> > Thanks in advance
> > Leonardo
> I don't believe that's possible with a true cluster - in a cluster, if one
> node goes down, its resources are immediately taken over by another node.
A
> cluster is intended for when you need absolute minimum downtime, but if
you
> can take the time to apply log shipping scripts, then I guess for you some
> downtime is acceptable. In that case, why not just continue to use log
> shipping?
> But I must admit that I've never used clustering myself, so you might want
> to post in microsoft.public.sqlserver.clustering to see if you can get
some
> more specific comments.
> Simon

Friday, March 9, 2012

Nominal intrest in TSQL

I have to make a calculation based on the Excel Nominal function to get the nomanal Interest

Month

Quarter

Half year

per year

0,16516%

0,49629%

0,99505%

2,00%

0,24663%

0,74171%

1,48892%

3,00%

0,32737%

0,98534%

1,98039%

4,00%

0,40741%

1,22722%

2,46951%

5,00%

0,48676%

1,46738%

2,95630%

6,00%

0,56541%

1,70585%

3,44080%

7,00%

0,64340%

1,94265%

3,92305%

8,00%

0,72073%

2,17782%

4,40307%

9,00%

10%

0,79741%

2,41137%

4,88088%

10,00%

11%

0,87346%

2,64333%

5,35654%

11,00%

12%

0,94888%

2,87373%

5,83005%

12,00%

In Exell the formula goes like this:
Per Month =NOMINAL(aIntrest;12)/12
Per Quarter =NOMINAL(aIntrest;4)/4
Half Year =NOMINAL(aIntrest;2)/2
Per Year =NOMINAL(aIntrest;1)

I would be very happy to get help on this.

I'm not entirely sure, if Excel's NOMINAL is working that way, however you can use the following to get the same results

DECLARE @.apr FLOAT
DECLARE @.frequency FLOAT

SET @.apr = 0.02
SET @.frequency = 12
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Monthly
SET @.frequency = 4
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Quarterly
SET @.frequency = 2
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Semiannual
SET @.frequency = 1
SELECT (@.frequency * POWER(1+@.apr, 1/@.frequency)-@.frequency)/@.frequency AS Annual
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
|||

Super

Thanks, so simple

Monday, February 20, 2012

No support for SQL Server Authentication and SSIS !!!!

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No Suitable Driver

Hi

I installed the free software MS SQL Server 2000 from microsoft.com.
I installed the JDBC driver from the following microsoft link
http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp?url=/MSDN-FILES/027/001/779/msdncompositedoc.xml

I set the classpath to point to the jar files in the lib folder in the driver I downloaded.

I wrote the following test file to test the connectivity import java.io.*;
import java.sql.*;

public class Test
{

public static final String DB_URL = "jdbc:JDataConnect://localhost/testdb";
public static final String DB_USERNAME = "";
public static final String DB_PASSWORD = "";
public static final String DB_DRIVER = "com.microsoft.jdbc.base.BaseDriver";

public static void main(String args[])
{

ResultSet rs = null;
try
{
Class.forName (DB_DRIVER);
Connection conn=DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

System.out.println("Connection established");
}
catch(Exception e)
{
System.out.println("An exception has occurred" +e);
}
}
}

when I run this code using java Test after successful compilation I get the following error

An exception has occurredjava.sql.SQLException: No suitable driver

Please Help !!!!

Thanks in Advance

PlumTry this

it working for me

import java.io.*;
import java.sql.*;

public class Test
{

public static final String DB_URL = "jdbc:microsoft:sqlserver://server1:port1";
public static final String DB_USERNAME = "sa";
public static final String DB_PASSWORD = "a108810";
public static final String DB_DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

public static void main(String args[])
{

ResultSet rs = null;
try
{
Class.forName (DB_DRIVER);
Connection conn=DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

System.out.println("Connection established");
}
catch(Exception e)
{
System.out.println("An exception has occurred" +e);
}
}
}|||When I tried to run your Test code.

An exception has occurredjava.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

I setup CLASSPATH to the 3 jar files which downloaded from microsoft.com, and I do not know WHY??

James|||the same siatuation happened to me
sometimes drivers cannot be found in jar files
try unpack the jar files in your work directory

Originally posted by james_hu
When I tried to run your Test code.

An exception has occurredjava.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

I setup CLASSPATH to the 3 jar files which downloaded from microsoft.com, and I do not know WHY??

James|||Hi,

My problem solved, I put space between ";" for CLASSPATH.
classpath=.; c:\..\msbase.jar; c:\..

MUST be classpath=.;c:\..\msbase.jar;c:\..
NO SPACE for classpath!!!

This is stupid JAVA, it should be improve later, this problem wastes my huge time!!!

I am not sure this is your prolem.

James

Friday, March 30, 2012

NOSKIP & SKIP make no difference to the amount of time backup take

NOSKIP & SKIP make no difference to the amount of time backup take

Friday, March 23, 2012

Noob question regarding "Data Warehouse"

noob alert! What i thought was a real simple query...

Tuesday, March 20, 2012

Nonclustered UNIQUE INDEX

Nonclustered UNIQUE INDEX

Monday, March 12, 2012

Non sharing data cluster

Friday, March 9, 2012

Nominal intrest in TSQL

Monday, February 20, 2012

No support for SQL Server Authentication and SSIS !!!!

No Suitable Driver

interface supported

Blog Archive

About Me