SQL tool for MSDE: related

Showing posts with label related. Show all posts

Sunday, March 11, 2012

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

I have a fact table with a single related dimension. The dimension is for a set of products, with a four-level user hierarchy. The fact table has a measure that only makes sense at the lowest level of the product hierarchy. Thus, when designing the measure group, I created a measure from the fact table and set the AggregateFunction on the measure to "None". I expected to be able to browse down the product hierarchy and see the value of the measure at the lowest level of the hierarchy. Unfortunately, I don't see any values (everything appears to be Null).

To ensure the values were getting read, I created a second measure in the measure group using the AggregateFunction of "Sum". When I browse the product hierarchy, I indeed see the sum of the measure and I see the individual measure values if I drill down to the lowest level of the hiearchy.

Thus, it would appear to me that the AggregateFunction of "None" is not working properly. Or perhaps I am interpreting its use incorrectly (or doing something else wrong). But with a single fact table that has a single dimension, its not very complicated!

TIA for any help...

Dave Fackler

Hi Dave,

The "None" aggregations seems to return a value if there's only a single fact record at that leaf of the measure group (SP1). I seem to recall back in the days of RTM that, if there were multiple fact records at a leaf, their "Sum" was returned, rather than null. However, this no longer worked when a fact dimension was configured.

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Did you get an answer to this issue? I have recently tried using AggregateFunction = None, and I to expected the function to behave as you described. I have a dimension which has a one-to-one mapping to the fact table, yet I am still unable to view the measure data.

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

TIA for any help...

Dave Fackler

Hi Dave,

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

AggregateFunction Does "None" Work?

Does the AggregateFunction of "None" actually work?

TIA for any help...

Dave Fackler

Hi Dave,

Hopefully, someone from MS can corroborate this behavior - maybe it's by design?

|||

Hi Dave/All

Would anyone be able to advise how the Non function should be used?

Regards, Matt

|||

Hey All,

I'm having the same issue..Aggregate Function "None" doesnt work ? Any one got it to work...

I have a boolean one-one measure and want to display True/False

Thanks

Sunday, February 12, 2012

Affinity mask setting in Yukon.

Hello,
Current setup: 3 instances of SQL server 2005 on a 2 CPU box.
How do I use IO/affinity to ensure that all SQL Server related I/O goes to
the first CPU? I'm aware that sp_configure is the command to be used.
But how do I exactly specify that the first CPU is to be exclusively used
for SQLServer I/O? Can someone give me an example OR point me to an page tha
t
has more info on this?
Cheers!
SQLCatzThe easiest way is to set the masks graphically in SSMS (Server Properties,
Processors).
There are more technical details about the whole process at
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Unfortunately I cannot use the GUI for this.
It has to be part of a script.
Which means that I have to use sp_configure.
But how do I specify which CPU gets the SQL Server related I/O?
Cheers!
SQLCatz|||For the first proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 1;
RECONFIGURE;
GO
For the second proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 2;
RECONFIGURE;
GO
For the both procs, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 3;
RECONFIGURE;
GO
Similarly for the affinity I/O mask.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thank you for that!
Recall reading on a web site that the setting was to be done using a bitmap
mask, where each bit specified the CPU number. Is this correct? And if it is
- how do I go about using this option? Just curious.
Cheers!
SQLCatz|||Please have a look at the link in my first post - htere's a good explanation
of the conversion needed for bitmasks and decimal values. The only tricky
thing to remember is that the processors are zero-indexed ie the first
processor is number 0 and the second is number 1 etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Affinity mask setting in Yukon.

Hello,
Current setup: 3 instances of SQL server 2005 on a 2 CPU box.
How do I use IO/affinity to ensure that all SQL Server related I/O goes to
the first CPU? I'm aware that sp_configure is the command to be used.
But how do I exactly specify that the first CPU is to be exclusively used
for SQLServer I/O? Can someone give me an example OR point me to an page that
has more info on this?
Cheers!
SQLCatzThe easiest way is to set the masks graphically in SSMS (Server Properties,
Processors).
There are more technical details about the whole process at
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Unfortunately I cannot use the GUI for this.
It has to be part of a script.
Which means that I have to use sp_configure.
But how do I specify which CPU gets the SQL Server related I/O?
Cheers!
SQLCatz|||For the first proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 1;
RECONFIGURE;
GO
For the second proc only, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 2;
RECONFIGURE;
GO
For the both procs, it'll be
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 3;
RECONFIGURE;
GO
Similarly for the affinity I/O mask.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Hello Paul,
Thank you for that!
Recall reading on a web site that the setting was to be done using a bitmap
mask, where each bit specified the CPU number. Is this correct? And if it is
- how do I go about using this option? Just curious.
Cheers!
SQLCatz|||Please have a look at the link in my first post - htere's a good explanation
of the conversion needed for bitmasks and decimal values. The only tricky
thing to remember is that the processors are zero-indexed ie the first
processor is number 0 and the second is number 1 etc.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

affinity mask question

Hello:
I took the MCDBA exam last week. I almost got over the hump, but one
question on the test (related to the Subject

left completely
stumped. If you can provide any help, I would be most appreciative.
The gist of the question is:
You manage a server with 4-processors and 1GB of RAM. There are
performance problems, so you get some perfmon statistics. The values
you get are 90% Processor Time, 95% User time and 20% Privilege
Time. How should you approach this problem:
A. go to 2GB RAM
B. go to 4GB RAM
C. change the affinity mask to only use processors 0,1,2
C. change the affinity mask to only use processors 1,2,3
The only part of this problem (I think) I understand would be: if
the Privilege time is high, then the system is spending too much
time doing "cluster" work rather than "user" (i.e. SQLS2k) work.
But that is not the case in this problem.
Rather than just the correct answer, I need to know how to approach
a problem like this. So, any BOL aricles or links about this would
be most helpful.
TIA.
Regards..
I'm not a DBA, however I am a SysAdmin /Lead Tech for a datacenter and part
of my routine is dealing with DBs that arent running as well as they should
be.
The question seems perfectly useless to me in typical MS test style. There
is not enough information to even begin to look at solving the problem. I
have never had cause to set affinity other than testing. Sounds like a bad
work around to me.
My real world approach would be
1.) Ram is cheep and 1gig of ram is weak in any DB. SQL std uses 2gig, so as
a minimum I would start with 3gigs regardless of performance. If I knew it
was SQL Ent I would use 4gig + depending on the version of os.
2.) A modern system tends to bottleneck on disk before cpu. I would run
perfmon on cpu, disk, memory in use, pagefile, and connections for starters.
There are several ways to address disk performance if that is actually the
problem.
3.) Most of the time a box is at or near 100% cpu its loopy code or a
runaway process. Rarely does a well maintained system hit 100% cpu for any
length of time unless there is something known to be cpu intensive being
run, and that should always be done during low use time.
4.) Throwing more hardware at a problem is usually everyones first choice,
and rarely solves the performance issue.
5.) In my opinion the best way to add more hardware is to add a whole new
system and move some of the databases to that system as most DB servers I
see are made of multiple smaller databases.
6.) Bug the DBA/developer until he fixes his broken code.
7.) Bug the DBA/developer until he fixes his broken code.
8.) Bug the DBA/developer until he fixes his broken code.
9.) Bug the DBA/developer until he fixes his broken code.
10.) Bug the DBA/developer until he fixes his broken code.
YMMV,
thanks,
DaveV
"Relishguy" <dbsearch04@.yahoo.com> wrote in message
news:84e6fe3d.0406261305.6b371af4@.posting.google.c om...
> Hello:
> I took the MCDBA exam last week. I almost got over the hump, but one
> question on the test (related to the Subject

left completely
> stumped. If you can provide any help, I would be most appreciative.
> The gist of the question is:
> You manage a server with 4-processors and 1GB of RAM. There are
> performance problems, so you get some perfmon statistics. The values
> you get are 90% Processor Time, 95% User time and 20% Privilege
> Time. How should you approach this problem:
> A. go to 2GB RAM
> B. go to 4GB RAM
> C. change the affinity mask to only use processors 0,1,2
> C. change the affinity mask to only use processors 1,2,3
> The only part of this problem (I think) I understand would be: if
> the Privilege time is high, then the system is spending too much
> time doing "cluster" work rather than "user" (i.e. SQLS2k) work.
> But that is not the case in this problem.
> Rather than just the correct answer, I need to know how to approach
> a problem like this. So, any BOL aricles or links about this would
> be most helpful.
> TIA.
> Regards..
|||I'm not a DBA, however I am a SysAdmin /Lead Tech for a datacenter and part
of my routine is dealing with DBs that arent running as well as they should
be.
The question seems perfectly useless to me in typical MS test style. There
is not enough information to even begin to look at solving the problem. I
have never had cause to set affinity other than testing. Sounds like a bad
work around to me.
My real world approach would be
1.) Ram is cheep and 1gig of ram is weak in any DB. SQL std uses 2gig, so as
a minimum I would start with 3gigs regardless of performance. If I knew it
was SQL Ent I would use 4gig + depending on the version of os.
2.) A modern system tends to bottleneck on disk before cpu. I would run
perfmon on cpu, disk, memory in use, pagefile, and connections for starters.
There are several ways to address disk performance if that is actually the
problem.
3.) Most of the time a box is at or near 100% cpu its loopy code or a
runaway process. Rarely does a well maintained system hit 100% cpu for any
length of time unless there is something known to be cpu intensive being
run, and that should always be done during low use time.
4.) Throwing more hardware at a problem is usually everyones first choice,
and rarely solves the performance issue.
5.) In my opinion the best way to add more hardware is to add a whole new
system and move some of the databases to that system as most DB servers I
see are made of multiple smaller databases.
6.) Bug the DBA/developer until he fixes his broken code.
7.) Bug the DBA/developer until he fixes his broken code.
8.) Bug the DBA/developer until he fixes his broken code.
9.) Bug the DBA/developer until he fixes his broken code.
10.) Bug the DBA/developer until he fixes his broken code.
YMMV,
thanks,
DaveV
"Relishguy" <dbsearch04@.yahoo.com> wrote in message
news:84e6fe3d.0406261305.6b371af4@.posting.google.c om...
> Hello:
> I took the MCDBA exam last week. I almost got over the hump, but one
> question on the test (related to the Subject

Thursday, February 9, 2012

Advise Please

hello
I have two related table

table1
cId
cDesc

table2
Id
Name
phone
cId

These two tables are related
where cId in table 1 is primary key and cId in table2 is foreign key

If I want to delete a record in table 1 which is related to table 2
Which is faster and more accurate

should I write my stored procedure like this
if exists(select * from table2 where cID = @.CID)
delete from table1 where cID = @.CID
else
return 0
-------------
or
-------------
delete from table1 where cID = @.CID

if @.@.error<>0
return 0

Neither of the above will work if you have a primary-foriegn key relationship as it would break the relationship.

Are you wanting to delete the record from table1 and any related records from table2, or do you want to delete from table1 only if there are no references to it in table2?

|||

wel i gues option 2 is faster but first u need to delete it from table 2 otherwise it gives u error

|||

Hi,

I recently ran into such a case and used the following code:
DELETE FROM TABLE2 WHERE id = @.ID
DELETE FROM TABLE1 WHERE id = @.ID
this worked perfectly for me.
But there is other way to achieve your goal:
just define the following foreign key on table2:
FOREIGN KEY (Id) REFERENCES table1 (cID) ON DELETE CASCADE

I hope this heps

|||

The second one

|||

I dont want to delete child if it exists...

Which the better way referenced to my first post

|||

according to my knowledge u need yo delete the refrence from child table i.e. foreign key relation ......

may be im wrong do let me know

|||

It seems that there is a big misunderstanding,

wht I am trying to do is :

check if the parent table has a child, if so message the user u cannot delete (The parent Record)

if the parent does not have a child delete the record..(The parent Record)

So I mentioned in my first post 2 options to do that and I was wondering which is the better and the faster

I hope I made my idea clear now
Thank you

|||

The 2nd option is indeed faster and better depending upon how many rows are in your 2nd table.

|||But is it apropriate to coz sql an error??|||

As long as your trapping the error and resetting the error object I dont see any problems with it. Just be sure you have the FK constraints on your second table otherwise this method will not work.

|||

well now v get the xact n accurate situation dat wht u need n wht u r doing...wel i gues u r on the rite track n again the 2nd option is beter wen eva u get error from sql u can catch it in try catch block n after verifying that its a FK constraint error u can simply display that child record exists.....

|||

Are there any disadvantages using catch block statments??

|||

Are there any disadvantages using "try catch" block statments??

|||

Using Try Catch block statements are highly advisable when you know that there is a possablity of an exception being thrown. Good error handling is always a good idea in any application.

Advice on upgrading 2000 to 2005 needed

Hi,
I've read through the threads related to 2000-2005 upgrade I can find on
this newsgroup. From what I've gathered, seems there are the following three
ways to upgrade.
1. in place
2. install a new instance that runs 2005 on the same database server
3. Set up a different server and then install 2005 on it.
We're currently running SQL 2000 SP3 on windows 2003. Is it true that
installing SP4 on SQL 2000 is required before it can be brought up to 2005
for in place upgrade?
For the rest two, I'm not very clear which one is better.
If we do option 3, we need to make DNS changes for server IP/name move which
always doesn't happen right away in my environment. That would most likely
extend upgrade time. But the obvious benefit is in case something wrong
happens with the upgrade, I can have the original 2000 server to safely go
back to.
For option 2, would a new 2005 instance have any negative impact on the sql
2000 instance? Are them totally independent of each other? I need to know
for sure if the 2005 instance doesn't work, the 2000 still works fine.
I'd appreciate any insight or real world experience (better) regarding 2005
upgrade. Things don't always go the way as they are instructed in the doc.
Thanks,
BingUpgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
SP3.
About a side-by-side installation of SQL Server 2005, i'm currently running
on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
2005 instances; since the installation i've never had a problem.
The only important consideration in this case is: do i have sufficient RAM
for both instances? Surely you'll need to configure appropriately Min memory
and Max memory for each instance.
More: currently i'm administering my SQL Server 2000 instances from SQL
Server Management Studio.
Gilberto Zampatti
"bing" wrote:
> Hi,
> I've read through the threads related to 2000-2005 upgrade I can find on
> this newsgroup. From what I've gathered, seems there are the following three
> ways to upgrade.
> 1. in place
> 2. install a new instance that runs 2005 on the same database server
> 3. Set up a different server and then install 2005 on it.
> We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> installing SP4 on SQL 2000 is required before it can be brought up to 2005
> for in place upgrade?
> For the rest two, I'm not very clear which one is better.
> If we do option 3, we need to make DNS changes for server IP/name move which
> always doesn't happen right away in my environment. That would most likely
> extend upgrade time. But the obvious benefit is in case something wrong
> happens with the upgrade, I can have the original 2000 server to safely go
> back to.
> For option 2, would a new 2005 instance have any negative impact on the sql
> 2000 instance? Are them totally independent of each other? I need to know
> for sure if the 2005 instance doesn't work, the 2000 still works fine.
> I'd appreciate any insight or real world experience (better) regarding 2005
> upgrade. Things don't always go the way as they are instructed in the doc.
> Thanks,
> Bing|||Thanks for the response. RAM allocation is a very good point. Our SQL
server 2000 server (Standard) which is running only one instance currently
has 2G RAM. If I install 2005 (Standard) on the save server, that will
compete with 2000 for RAM.
Bing
"Gilberto Zampatti" wrote:
> Upgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
> SP3.
> About a side-by-side installation of SQL Server 2005, i'm currently running
> on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
> 2005 instances; since the installation i've never had a problem.
> The only important consideration in this case is: do i have sufficient RAM
> for both instances? Surely you'll need to configure appropriately Min memory
> and Max memory for each instance.
> More: currently i'm administering my SQL Server 2000 instances from SQL
> Server Management Studio.
> Gilberto Zampatti
> "bing" wrote:
> > Hi,
> >
> > I've read through the threads related to 2000-2005 upgrade I can find on
> > this newsgroup. From what I've gathered, seems there are the following three
> > ways to upgrade.
> >
> > 1. in place
> > 2. install a new instance that runs 2005 on the same database server
> > 3. Set up a different server and then install 2005 on it.
> >
> > We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> > installing SP4 on SQL 2000 is required before it can be brought up to 2005
> > for in place upgrade?
> >
> > For the rest two, I'm not very clear which one is better.
> >
> > If we do option 3, we need to make DNS changes for server IP/name move which
> > always doesn't happen right away in my environment. That would most likely
> > extend upgrade time. But the obvious benefit is in case something wrong
> > happens with the upgrade, I can have the original 2000 server to safely go
> > back to.
> >
> > For option 2, would a new 2005 instance have any negative impact on the sql
> > 2000 instance? Are them totally independent of each other? I need to know
> > for sure if the 2005 instance doesn't work, the 2000 still works fine.
> >
> > I'd appreciate any insight or real world experience (better) regarding 2005
> > upgrade. Things don't always go the way as they are instructed in the doc.
> >
> > Thanks,
> >
> > Bing|||On May 18, 9:49 am, bing <b...@.discussions.microsoft.com> wrote:
> Thanks for the response. RAM allocation is a very good point. Our SQL
> server 2000 server (Standard) which is running only one instance currently
> has 2G RAM. If I install 2005 (Standard) on the save server, that will
> compete with 2000 for RAM.
> Bing
>
> "Gilberto Zampatti" wrote:
> > Upgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
> > SP3.
> > About a side-by-side installation of SQL Server 2005, i'm currently running
> > on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
> > 2005 instances; since the installation i've never had a problem.
> > The only important consideration in this case is: do i have sufficient RAM
> > for both instances? Surely you'll need to configure appropriately Min memory
> > and Max memory for each instance.
> > More: currently i'm administering my SQL Server 2000 instances from SQL
> > Server Management Studio.
> > Gilberto Zampatti
> > "bing" wrote:
> > > Hi,
> > > I've read through the threads related to 2000-2005 upgrade I can find on
> > > this newsgroup. From what I've gathered, seems there are the following three
> > > ways to upgrade.
> > > 1. in place
> > > 2. install a new instance that runs 2005 on the same database server
> > > 3. Set up a different server and then install 2005 on it.
> > > We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> > > installing SP4 on SQL 2000 is required before it can be brought up to 2005
> > > for in place upgrade?
> > > For the rest two, I'm not very clear which one is better.
> > > If we do option 3, we need to make DNS changes for server IP/name move which
> > > always doesn't happen right away in my environment. That would most likely
> > > extend upgrade time. But the obvious benefit is in case something wrong
> > > happens with the upgrade, I can have the original 2000 server to safely go
> > > back to.
> > > For option 2, would a new 2005 instance have any negative impact on the sql
> > > 2000 instance? Are them totally independent of each other? I need to know
> > > for sure if the 2005 instance doesn't work, the 2000 still works fine.
> > > I'd appreciate any insight or real world experience (better) regarding 2005
> > > upgrade. Things don't always go the way as they are instructed in the doc.
> > > Thanks,
> > > Bing- Hide quoted text -
> - Show quoted text -
Here is my experience in a nutshell - as much as I remember anyway. I
just did a 2000 to 2005 upgrade for 8 production databases varying
from a few hundred megs to 50 gigs.This approach with a new server
allowed us to test and to hold cutting over until we were 100% sure
everything was working. There are may ways to do this, but this how I
did it....
We built and configured a new 2005 server first. Here is the overview
of the check list:
1. Build new server with network engineers.
2. Discuss best place to keep logfiles, databases, backups etc. Do
appropriate sizing etc.
3. Decide what new services to use and get them configured and
running. EX: We are not using analysis service.
4. Configure database mail.
5. Configure alerts and get backups going for system databases etc.
6. do a backup and restore from 2000 to 2005 and get the db backup and
log maintenence jobs going. I did weekly stats update and alter index
reorganize.
7. Created all windows, sql logins on the new box - we are mixed mode.
8. This allowed us to test the apps on the new server and permissions
etc. The schema's can be troublesome. I dropped all users after
restoring and then reapplied the permissions.
9.Here was my actual cutover checklist of things the SSIS package did
a. take down apps/or web server during cutover
b.backup 2000 databases to unc path
c.resotore databases to new 2005 box from unc path.
d.drop permissions (logins, schemas, roles, users)
e. re-assign permissions as required.
f.rebuild indexes
g.set database compatibility level (90) for 2005
h.re-point all applications to new SQL instance
i.run backup and maintenence jobs to make sure all working.
j.test and run other SSIS jobs
k.detach old 2005 databases but leave on the server for awhile in case
of issues.
Kristina|||"Kristina" wrote:
> On May 18, 9:49 am, bing <b...@.discussions.microsoft.com> wrote:
> > Thanks for the response. RAM allocation is a very good point. Our SQL
> > server 2000 server (Standard) which is running only one instance currently
> > has 2G RAM. If I install 2005 (Standard) on the save server, that will
> > compete with 2000 for RAM.
> >
> > Bing
> >
> >
> >
> > "Gilberto Zampatti" wrote:
> > > Upgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
> > > SP3.
> > > About a side-by-side installation of SQL Server 2005, i'm currently running
> > > on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
> > > 2005 instances; since the installation i've never had a problem.
> > > The only important consideration in this case is: do i have sufficient RAM
> > > for both instances? Surely you'll need to configure appropriately Min memory
> > > and Max memory for each instance.
> > > More: currently i'm administering my SQL Server 2000 instances from SQL
> > > Server Management Studio.
> > > Gilberto Zampatti
> >
> > > "bing" wrote:
> >
> > > > Hi,
> >
> > > > I've read through the threads related to 2000-2005 upgrade I can find on
> > > > this newsgroup. From what I've gathered, seems there are the following three
> > > > ways to upgrade.
> >
> > > > 1. in place
> > > > 2. install a new instance that runs 2005 on the same database server
> > > > 3. Set up a different server and then install 2005 on it.
> >
> > > > We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> > > > installing SP4 on SQL 2000 is required before it can be brought up to 2005
> > > > for in place upgrade?
> >
> > > > For the rest two, I'm not very clear which one is better.
> >
> > > > If we do option 3, we need to make DNS changes for server IP/name move which
> > > > always doesn't happen right away in my environment. That would most likely
> > > > extend upgrade time. But the obvious benefit is in case something wrong
> > > > happens with the upgrade, I can have the original 2000 server to safely go
> > > > back to.
> >
> > > > For option 2, would a new 2005 instance have any negative impact on the sql
> > > > 2000 instance? Are them totally independent of each other? I need to know
> > > > for sure if the 2005 instance doesn't work, the 2000 still works fine.
> >
> > > > I'd appreciate any insight or real world experience (better) regarding 2005
> > > > upgrade. Things don't always go the way as they are instructed in the doc.
> >
> > > > Thanks,
> >
> > > > Bing- Hide quoted text -
> >
> > - Show quoted text -
> Here is my experience in a nutshell - as much as I remember anyway. I
> just did a 2000 to 2005 upgrade for 8 production databases varying
> from a few hundred megs to 50 gigs.This approach with a new server
> allowed us to test and to hold cutting over until we were 100% sure
> everything was working. There are may ways to do this, but this how I
> did it....
> We built and configured a new 2005 server first. Here is the overview
> of the check list:
> 1. Build new server with network engineers.
> 2. Discuss best place to keep logfiles, databases, backups etc. Do
> appropriate sizing etc.
> 3. Decide what new services to use and get them configured and
> running. EX: We are not using analysis service.
> 4. Configure database mail.
> 5. Configure alerts and get backups going for system databases etc.
> 6. do a backup and restore from 2000 to 2005 and get the db backup and
> log maintenence jobs going. I did weekly stats update and alter index
> reorganize.
> 7. Created all windows, sql logins on the new box - we are mixed mode.
> 8. This allowed us to test the apps on the new server and permissions
> etc. The schema's can be troublesome. I dropped all users after
> restoring and then reapplied the permissions.
> 9.Here was my actual cutover checklist of things the SSIS package did
> a. take down apps/or web server during cutover
> b.backup 2000 databases to unc path
> c.resotore databases to new 2005 box from unc path.
> d.drop permissions (logins, schemas, roles, users)
> e. re-assign permissions as required.
> f.rebuild indexes
> g.set database compatibility level (90) for 2005
> h.re-point all applications to new SQL instance
> i.run backup and maintenence jobs to make sure all working.
> j.test and run other SSIS jobs
> k.detach old 2005 databases but leave on the server for awhile in case
> of issues.
> Kristina
>
Excellent! Thanks much. We're in a similar situation. For the last step
k, I think you meant 'detach old 2000 databases', right?
Bing|||On May 18, 11:11 am, bing <b...@.discussions.microsoft.com> wrote:
> "Kristina" wrote:
> > On May 18, 9:49 am, bing <b...@.discussions.microsoft.com> wrote:
> > > Thanks for the response. RAM allocation is a very good point. Our SQL
> > > server 2000 server (Standard) which is running only one instance currently
> > > has 2G RAM. If I install 2005 (Standard) on the save server, that will
> > > compete with 2000 for RAM.
> > > Bing
> > > "Gilberto Zampatti" wrote:
> > > > Upgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
> > > > SP3.
> > > > About a side-by-side installation of SQL Server 2005, i'm currently running
> > > > on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
> > > > 2005 instances; since the installation i've never had a problem.
> > > > The only important consideration in this case is: do i have sufficient RAM
> > > > for both instances? Surely you'll need to configure appropriately Min memory
> > > > and Max memory for each instance.
> > > > More: currently i'm administering my SQL Server 2000 instances from SQL
> > > > Server Management Studio.
> > > > Gilberto Zampatti
> > > > "bing" wrote:
> > > > > Hi,
> > > > > I've read through the threads related to 2000-2005 upgrade I can find on
> > > > > this newsgroup. From what I've gathered, seems there are the following three
> > > > > ways to upgrade.
> > > > > 1. in place
> > > > > 2. install a new instance that runs 2005 on the same database server
> > > > > 3. Set up a different server and then install 2005 on it.
> > > > > We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> > > > > installing SP4 on SQL 2000 is required before it can be brought up to 2005
> > > > > for in place upgrade?
> > > > > For the rest two, I'm not very clear which one is better.
> > > > > If we do option 3, we need to make DNS changes for server IP/name move which
> > > > > always doesn't happen right away in my environment. That would most likely
> > > > > extend upgrade time. But the obvious benefit is in case something wrong
> > > > > happens with the upgrade, I can have the original 2000 server to safely go
> > > > > back to.
> > > > > For option 2, would a new 2005 instance have any negative impact on the sql
> > > > > 2000 instance? Are them totally independent of each other? I need to know
> > > > > for sure if the 2005 instance doesn't work, the 2000 still works fine.
> > > > > I'd appreciate any insight or real world experience (better) regarding 2005
> > > > > upgrade. Things don't always go the way as they are instructed in the doc.
> > > > > Thanks,
> > > > > Bing- Hide quoted text -
> > > - Show quoted text -
> > Here is my experience in a nutshell - as much as I remember anyway. I
> > just did a 2000 to 2005 upgrade for 8 production databases varying
> > from a few hundred megs to 50 gigs.This approach with a new server
> > allowed us to test and to hold cutting over until we were 100% sure
> > everything was working. There are may ways to do this, but this how I
> > did it....
> > We built and configured a new 2005 server first. Here is the overview
> > of the check list:
> > 1. Build new server with network engineers.
> > 2. Discuss best place to keep logfiles, databases, backups etc. Do
> > appropriate sizing etc.
> > 3. Decide what new services to use and get them configured and
> > running. EX: We are not using analysis service.
> > 4. Configure database mail.
> > 5. Configure alerts and get backups going for system databases etc.
> > 6. do a backup and restore from 2000 to 2005 and get the db backup and
> > log maintenence jobs going. I did weekly stats update and alter index
> > reorganize.
> > 7. Created all windows, sql logins on the new box - we are mixed mode.
> > 8. This allowed us to test the apps on the new server and permissions
> > etc. The schema's can be troublesome. I dropped all users after
> > restoring and then reapplied the permissions.
> > 9.Here was my actual cutover checklist of things the SSIS package did
> > a. take down apps/or web server during cutover
> > b.backup 2000 databases to unc path
> > c.resotore databases to new 2005 box from unc path.
> > d.drop permissions (logins, schemas, roles, users)
> > e. re-assign permissions as required.
> > f.rebuild indexes
> > g.set database compatibility level (90) for 2005
> > h.re-point all applications to new SQL instance
> > i.run backup and maintenence jobs to make sure all working.
> > j.test and run other SSIS jobs
> > k.detach old 2005 databases but leave on the server for awhile in case
> > of issues.
> > Kristina
> Excellent! Thanks much. We're in a similar situation. For the last step
> k, I think you meant 'detach old 2000 databases', right?
> Bing- Hide quoted text -
> - Show quoted text -
yes, I am a poor writer.....it wasn't really that bad to do the
upgrade. I got the wrox press SQL 2005 Administration book and it
helped tons...
Good LUCK!|||"Kristina" wrote:
> On May 18, 11:11 am, bing <b...@.discussions.microsoft.com> wrote:
> > "Kristina" wrote:
> > > On May 18, 9:49 am, bing <b...@.discussions.microsoft.com> wrote:
> > > > Thanks for the response. RAM allocation is a very good point. Our SQL
> > > > server 2000 server (Standard) which is running only one instance currently
> > > > has 2G RAM. If I install 2005 (Standard) on the save server, that will
> > > > compete with 2000 for RAM.
> >
> > > > Bing
> >
> > > > "Gilberto Zampatti" wrote:
> > > > > Upgrade supported by SQL Server 2005 include SQL Server 7.0 SP$ and SQL 2000
> > > > > SP3.
> > > > > About a side-by-side installation of SQL Server 2005, i'm currently running
> > > > > on my laptop (2Gb RAM...) two SQL Server 2000 instances and two SQL Server
> > > > > 2005 instances; since the installation i've never had a problem.
> > > > > The only important consideration in this case is: do i have sufficient RAM
> > > > > for both instances? Surely you'll need to configure appropriately Min memory
> > > > > and Max memory for each instance.
> > > > > More: currently i'm administering my SQL Server 2000 instances from SQL
> > > > > Server Management Studio.
> > > > > Gilberto Zampatti
> >
> > > > > "bing" wrote:
> >
> > > > > > Hi,
> >
> > > > > > I've read through the threads related to 2000-2005 upgrade I can find on
> > > > > > this newsgroup. From what I've gathered, seems there are the following three
> > > > > > ways to upgrade.
> >
> > > > > > 1. in place
> > > > > > 2. install a new instance that runs 2005 on the same database server
> > > > > > 3. Set up a different server and then install 2005 on it.
> >
> > > > > > We're currently running SQL 2000 SP3 on windows 2003. Is it true that
> > > > > > installing SP4 on SQL 2000 is required before it can be brought up to 2005
> > > > > > for in place upgrade?
> >
> > > > > > For the rest two, I'm not very clear which one is better.
> >
> > > > > > If we do option 3, we need to make DNS changes for server IP/name move which
> > > > > > always doesn't happen right away in my environment. That would most likely
> > > > > > extend upgrade time. But the obvious benefit is in case something wrong
> > > > > > happens with the upgrade, I can have the original 2000 server to safely go
> > > > > > back to.
> >
> > > > > > For option 2, would a new 2005 instance have any negative impact on the sql
> > > > > > 2000 instance? Are them totally independent of each other? I need to know
> > > > > > for sure if the 2005 instance doesn't work, the 2000 still works fine.
> >
> > > > > > I'd appreciate any insight or real world experience (better) regarding 2005
> > > > > > upgrade. Things don't always go the way as they are instructed in the doc.
> >
> > > > > > Thanks,
> >
> > > > > > Bing- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Here is my experience in a nutshell - as much as I remember anyway. I
> > > just did a 2000 to 2005 upgrade for 8 production databases varying
> > > from a few hundred megs to 50 gigs.This approach with a new server
> > > allowed us to test and to hold cutting over until we were 100% sure
> > > everything was working. There are may ways to do this, but this how I
> > > did it....
> >
> > > We built and configured a new 2005 server first. Here is the overview
> > > of the check list:
> > > 1. Build new server with network engineers.
> > > 2. Discuss best place to keep logfiles, databases, backups etc. Do
> > > appropriate sizing etc.
> > > 3. Decide what new services to use and get them configured and
> > > running. EX: We are not using analysis service.
> > > 4. Configure database mail.
> > > 5. Configure alerts and get backups going for system databases etc.
> > > 6. do a backup and restore from 2000 to 2005 and get the db backup and
> > > log maintenence jobs going. I did weekly stats update and alter index
> > > reorganize.
> > > 7. Created all windows, sql logins on the new box - we are mixed mode.
> > > 8. This allowed us to test the apps on the new server and permissions
> > > etc. The schema's can be troublesome. I dropped all users after
> > > restoring and then reapplied the permissions.
> > > 9.Here was my actual cutover checklist of things the SSIS package did
> > > a. take down apps/or web server during cutover
> > > b.backup 2000 databases to unc path
> > > c.resotore databases to new 2005 box from unc path.
> > > d.drop permissions (logins, schemas, roles, users)
> > > e. re-assign permissions as required.
> > > f.rebuild indexes
> > > g.set database compatibility level (90) for 2005
> > > h.re-point all applications to new SQL instance
> > > i.run backup and maintenence jobs to make sure all working.
> > > j.test and run other SSIS jobs
> > > k.detach old 2005 databases but leave on the server for awhile in case
> > > of issues.
> >
> > > Kristina
> >
> > Excellent! Thanks much. We're in a similar situation. For the last step
> > k, I think you meant 'detach old 2000 databases', right?
> >
> > Bing- Hide quoted text -
> >
> > - Show quoted text -
> yes, I am a poor writer.....it wasn't really that bad to do the
> upgrade. I got the wrox press SQL 2005 Administration book and it
> helped tons...
> Good LUCK!
>
Glad to hear it isn't that bad. Thanks again, Kristina.
Bing

Sunday, March 11, 2012

AggregateFunction Does "None" Work?

AggregateFunction Does "None" Work?

AggregateFunction Does "None" Work?

Sunday, February 12, 2012

Affinity mask setting in Yukon.

Affinity mask setting in Yukon.

affinity mask question

Thursday, February 9, 2012

Advise Please

Advice on upgrading 2000 to 2005 needed

Advice on upgrading 2000 to 2005 needed

Advice on upgrading 2000 to 2005 needed

SQL tool for MSDE

Blog Archive

About Me