How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
SpinIt's really no different from installing or un-installing the first
instance.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
--
Spin|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.|||None that I know of.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

How difficult is it to uninstall/re-install a second SQL named instance?

Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
It's really no different from installing or un-installing the first
instance.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f06eF2fjee4U1@.mid.individual.net...
Gurus,
Running SQL Server 2005 SP2 and using two named instances on a Windows
Server 2003 SP2 cluster. How difficult would it be to uninstall/re-install
the second SQL instance?
Spin
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.
|||None that I know of.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Spin" <Spin@.invalid.com> wrote in message
news:65f4a5F2dle3mU1@.mid.individual.net...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iVfXfAlIHA.6092@.TK2MSFTNGP06.phx.gbl...
> It's really no different from installing or un-installing the first
> instance.
> --
> Tom
What I'm worried about is pitfalls to avoid. I want to avoid messing
something up in my cluster.

How define users of another domain.

Hi everybody, i should have a little problem.
I must grant the access (using odbc) to users of domain "A" to a sql server
2000 instance running in another domain (domain "B").
Well, if the users read the database like user "sa" (defined in the odbc
connection) there aren't problems but if in the odbc connection is specified
the "trust connection" flag nobody can read data.
In sql server the users are defined both username and domain\username and
permissions are defined correctly.
What could i do?
Thanks in avance for your answers.
RobyHi
If your SQL Server is in Domain A, you need to setup a trust relationship
with Domain B at Active Directory level.
Then, when use "B\user1" presents it's credentials to the SQL Server, SQL
Server passes the request to the OS, which in turn passes it to Domain A's
domain controller, who in turn passes the request to Domain B's domain
controller.
The trusting is done at OS level. If you can not setup a trust, then you
need to keep using SQL Server Security.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"robyemme" <marautor@.tiscali.it> wrote in message
news:F1657283-F17A-40FC-9061-C0BFBA7DC569@.microsoft.com...
> Hi everybody, i should have a little problem.
> I must grant the access (using odbc) to users of domain "A" to a sql
> server
> 2000 instance running in another domain (domain "B").
> Well, if the users read the database like user "sa" (defined in the odbc
> connection) there aren't problems but if in the odbc connection is
> specified
> the "trust connection" flag nobody can read data.
> In sql server the users are defined both username and domain\username and
> permissions are defined correctly.
> What could i do?
> Thanks in avance for your answers.
> Roby
>

How could I notify new subscribers with previous data?

Hi!

I have my NS running. Subscribers are reciving notifications right. How could I send past notifications to new users . Could it be possible?

I think we have all the previous data (the event data table as the notification table) what do you think is the best way or approach to do it?

i.e:

Insert an action item asigned to a user in my DB. If the user has a subscription to it, NS generate a notification and send an email to the user. In my case this user is not a subscriber yet, I add him 2 days after. How could I send the notification thas had been created for him before?

Regards ;)

If I ever have this problem, I will probably play with checking the <myNSApplicationName>Notifications table and look for the data matching the same criteria as those specified by this "late" client in his subscription(s).

...unless it's been already vacuumed, of course.

|||

Assuming this is a scheduled subscription, you can use a chronicles tables to keep track of the last time a subscription was processed. For new subscriptions, the datetime column should be set to something like '1900-01-01 00:00:00.000' so that it will match everything.

After the match, you then update the LastProcessedTime column of the chronicles table with the current datetime. That way, future quantums will only send the most recently added event data.

HTH...

Joe

How could I notify new subscribers with previous data?

Hi!

I have my NS running. Subscribers are reciving notifications right. How could I send past notifications to new users . Could it be possible?

I think we have all the previous data (the event data table as the notification table) what do you think is the best way or approach to do it?

i.e:

Insert an action item asigned to a user in my DB. If the user has a subscription to it, NS generate a notification and send an email to the user. In my case this user is not a subscriber yet, I add him 2 days after. How could I send the notification thas had been created for him before?

Regards ;)

If I ever have this problem, I will probably play with checking the <myNSApplicationName>Notifications table and look for the data matching the same criteria as those specified by this "late" client in his subscription(s).

...unless it's been already vacuumed, of course.

|||

Assuming this is a scheduled subscription, you can use a chronicles tables to keep track of the last time a subscription was processed. For new subscriptions, the datetime column should be set to something like '1900-01-01 00:00:00.000' so that it will match everything.

After the match, you then update the LastProcessedTime column of the chronicles table with the current datetime. That way, future quantums will only send the most recently added event data.

HTH...

Joe

how come the validation doesn't seem to end keep on running?

Hi folks,

it is me again. Just want to enquire how come with the control flow of the Data flows been set appropriately, why when it is executed manually in the sql server 2005 file system, the validation shows the incorrect flow of data flows been executed and doesn't seem to end? Thanks in advance

Regards,

Ken

Hi Ken,

It's probably just me combined with the fact I've had no coffee yet, but I don't follow. Can you restate the issue?

Thanks,
Andy

|||

Hi Andy,

Thanks again for helping this newbie. Ok I have input my package into the SQL Server 2005 file system located under Stored Packages. I run them manually which displays a validation report on its progress. But i notice from the validation report that it doesn't run the validation check as how I drew the flow of the components and it seems to be running endlessly. Is there a way to edit the package so that it can signal the end of the package.

|||

Hi Ken,

You are most welcome - I was a newbie myself not long ago.

If I'm understanding you correctly, either your package is stuck in Validation (or appears to be) or stuck in execution (or appears to be).

If the package is stuck in validation, you can change the Package level property DelayValidation to True. The package will load and attempt to run. Any errors will likley cause it to crash, but it may give you insight into why it is behaving in this fashion.

If the package is stuck in execution, I suggest you implement logging. Also consider custom logging using Execute SQL Tasks on the Control Flow and in OnError event handlers.

Hope this helps,

Andy

|||Are you executing child packages from a parent package, by any chance?

How can you tell what DTS package a job step is running?

How can you tell what DTS package a Job step is running when it looks like this?

DTSRun /~Z0xBEA63421A37A7AA7175D23C17BA2CB8CEC94DCC69159320C62777706E248EFDD4BF43C93709C9FCEF2982201C4D26B7EAFF25816E9F0E7C51467E5C2446BE35FBA02E809F523C5D19F6AC91416E5ED3B79BE27B7991980910679EBF4C311A9A33AED799A9C53BB7BFDAEDE7EEBC6ECE9001D0E6218956A578C40FC

The GUID does not appear to be related to what DTS shows in the Package or Version GUIDS of the DTS packages. And yet the job runs from what I am told.

SQL Server 2000 msdb.dbo.sysjobsteps

The value "DTSRun /~Z0xBEA63421...." is in the command column. The table also has a step_name column which appears in the job. The default step_name is the name of the DTS package but it could have been renamed.

If so turn on profiler and find the package execution line with the package name

exec msdb..sp_get_dtspackage N'MyPackage'

|||If you can enable the dTS package log then you can see the status.|||

The /~Z paramater is an encrypted command line version. Microsoft does this in case it needs to pass a password.

The job step and job name is by default the name of the package. If these have been changed that will make it harder.

If you have the command line and run it from a command prompt it will display in the command window which DTS package is running. I would recommend using this information to change the command line to use the /S /E /N commands so that it's easier to see which package is running.

How can we avoid somebody to access the MDF data by doing User Instance connection?

I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).

For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.

But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.

How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?

Thanks!

Hi,

AFAIK their is no way to restrict access to MDF/LDF files physically... refer below thread which has discussion the same issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

Hemantgiri S. Goswami

|||

Alright, but what about the User Instance? Is there a way to prevent somebody of getting connected on a server using our MDF file and the User Instance option into his connection string?

|||No, you wil lhave to do this via NTFS permissions.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can the Profiler textdata field be enlarged?

When running Profiler to save a trace to a table the field 'TextData' gets
trunciated. The datatype for that field is as shown:

Column name | Data Type | Length | Allow Null

TextData ntext 16 yes

I can not find a way to set the trace up to create a tablewith TextData of
length greater than 16. This really causes a problem when trying to capture
long running queries for tuning as the query itself maybe truncated. Is
there a way around this?"Robert" <stop.spam@.boeing.com> wrote in message
news:HtAJF7.7yM@.news.boeing.com...
> When running Profiler to save a trace to a table the field 'TextData' gets
> trunciated. The datatype for that field is as shown:
> Column name | Data Type | Length | Allow Null
> TextData ntext 16 yes
>
> I can not find a way to set the trace up to create a tablewith TextData of
> length greater than 16. This really causes a problem when trying to
capture
> long running queries for tuning as the query itself maybe truncated. Is
> there a way around this?
>
ntext can store up to 1GB of Unicode text, so you should be able to see the
full query text. If not, perhaps you can clarify how you are viewing the
data? Using Query Analyzer, or another tool? QA displays only 256 characters
by default, so you may need to check your settings (Tools - Options -
Results).

By default, MSSQL stores a pointer to ntext data in the table, not the data
itself, which is where the 16 bytes comes from - that is the size of the
pointer only, not the data.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4033b59f$1_3@.news.bluewin.ch...
> "Robert" <stop.spam@.boeing.com> wrote in message
> news:HtAJF7.7yM@.news.boeing.com...
> > When running Profiler to save a trace to a table the field 'TextData'
gets
> > trunciated. The datatype for that field is as shown:
> > Column name | Data Type | Length | Allow Null
> > TextData ntext 16 yes
> > I can not find a way to set the trace up to create a tablewith TextData
of
> > length greater than 16. This really causes a problem when trying to
> capture
> > long running queries for tuning as the query itself maybe truncated. Is
> > there a way around this?
> ntext can store up to 1GB of Unicode text, so you should be able to see
the
> full query text. If not, perhaps you can clarify how you are viewing the
> data? Using Query Analyzer, or another tool? QA displays only 256
characters
> by default, so you may need to check your settings (Tools - Options -
> Results).
> By default, MSSQL stores a pointer to ntext data in the table, not the
data
> itself, which is where the 16 bytes comes from - that is the size of the
> pointer only, not the data.
> Simon

Thanx Simon

You nailed it! I am not worthy.

How can i TRUNCATE my table?

How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.

you need to execute a sql state like:

TRUNCATE TABLE myTable;

http://msdn2.microsoft.com/en-us/library/ms177570.aspx

I usually do this through query analyzer.

|||

my table is called 'objects'. i can view my records/data under by right clicking on my Table ('Objects') in the 'Server Explorer'.

but don't know where to place this SQL code so that i could execute it, could you please advice?

and for my code would it be;

TRUNCATE TABLE ObjectsTable;

|||

right click the table and select: new query

put the truncate command in place of the select statement.

dont save it - just run it

you'll get a warning about the truncate not being supported for a graphical display, but it can still be executed.

note: if your table is named 'objects' then the sql would be

TRUNCATE TABLE objects;

|||

I have used the code from your recommended website, but don't know what is my 'schema_name', could you please advice me where I can find this in my 'Server Explorer'?

this is what i have changed so far - is it right to leave the name as it is i.e.Objects_name ? also is it right to keep all the brackets and full stops?

TRUNCATE TABLE     [ { LTrails_name.[ schema_name ]. | schema_name . } ]    Objects_name[ ; ]

|||

if you right click your table and start to create a new query and add that table you'll get something like this:

SELECT FROM objects

the word 'objects' should already be your exact table name that you added to the query.

now you just need to replace the SELECT FROM portion of the query with TRUNCATE TABLE

TRUNCATETABLE objects

Then run the query.

|||

Your very detailed explanation is most appreciated,

How can I troubleshoot eratic, slow SQL Backup job?

I have a SQL 2000 sp3 server running on a server class machine with
local drives.
I'm backing up a 50 gig db from the e partition to the F: partition.
Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
There are no jobs running when the backups execute, no index rebuilds,
no veritas, no virus scans running.
I'll run a backup and it will take 20 minutes. I rerun it again
right away and it takes 2.5 hours.
I run a statistics IO trace, and the only difference is the cumulative
wait time...which is much much higher when the backup takes 2.5 hours.
Any suggestions on how to troubleshoot this? Or why it might be
happening?On Apr 16, 5:51 pm, "Sanctus" <mch...@.hotmail.com> wrote:
> I have a SQL 2000 sp3 server running on a server class machine with
> local drives.
> I'm backing up a 50 gig db from the e partition to the F: partition.
> Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
> There are no jobs running when the backups execute, no index rebuilds,
> no veritas, no virus scans running.
> I'll run a backup and it will take 20 minutes. I rerun it again
> right away and it takes 2.5 hours.
> I run a statistics IO trace, and the only difference is the cumulative
> wait time...which is much much higher when the backup takes 2.5 hours.
> Any suggestions on how to troubleshoot this? Or why it might be
> happening?
Here are some ideas...
E: to F: - are these different drives on a san and does the backup
have to go accross the network - higher network traffic at times?
Network saturation?
Are their heavier user processes at times? Heavy duty queries/long
running transactions?
Is this a dedicated SQL Server, could another application be hogging
the Memory or IO?
As you can see, I am just shooting blind. Maybe some of these ideas
can help you think how to investigate?

How can I troubleshoot eratic, slow SQL Backup job?

I have a SQL 2000 sp3 server running on a server class machine with
local drives.
I'm backing up a 50 gig db from the e partition to the F: partition.
Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
There are no jobs running when the backups execute, no index rebuilds,
no veritas, no virus scans running.
I'll run a backup and it will take 20 minutes. I rerun it again
right away and it takes 2.5 hours.
I run a statistics IO trace, and the only difference is the cumulative
wait time...which is much much higher when the backup takes 2.5 hours.
Any suggestions on how to troubleshoot this? Or why it might be
happening?On Apr 16, 5:51 pm, "Sanctus" <mch...@.hotmail.com> wrote:
> I have a SQL 2000 sp3 server running on a server class machine with
> local drives.
> I'm backing up a 50 gig db from the e partition to the F: partition.
> Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
> There are no jobs running when the backups execute, no index rebuilds,
> no veritas, no virus scans running.
> I'll run a backup and it will take 20 minutes. I rerun it again
> right away and it takes 2.5 hours.
> I run a statistics IO trace, and the only difference is the cumulative
> wait time...which is much much higher when the backup takes 2.5 hours.
> Any suggestions on how to troubleshoot this? Or why it might be
> happening?
Here are some ideas...
E: to F: - are these different drives on a san and does the backup
have to go accross the network - higher network traffic at times?
Network saturation?
Are their heavier user processes at times? Heavy duty queries/long
running transactions?
Is this a dedicated SQL Server, could another application be hogging
the Memory or IO?
As you can see, I am just shooting blind. Maybe some of these ideas
can help you think how to investigate?

How can I troubleshoot eratic, slow SQL Backup job?

I have a SQL 2000 sp3 server running on a server class machine with
local drives.
I'm backing up a 50 gig db from the e partition to the F: partition.
Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
There are no jobs running when the backups execute, no index rebuilds,
no veritas, no virus scans running.
I'll run a backup and it will take 20 minutes. I rerun it again
right away and it takes 2.5 hours.
I run a statistics IO trace, and the only difference is the cumulative
wait time...which is much much higher when the backup takes 2.5 hours.
Any suggestions on how to troubleshoot this? Or why it might be
happening?
On Apr 16, 5:51 pm, "Sanctus" <mch...@.hotmail.com> wrote:
> I have a SQL 2000 sp3 server running on a server class machine with
> local drives.
> I'm backing up a 50 gig db from the e partition to the F: partition.
> Sometimes the backup takes 20 minutes...sometimes it takes 2.5 hours.
> There are no jobs running when the backups execute, no index rebuilds,
> no veritas, no virus scans running.
> I'll run a backup and it will take 20 minutes. I rerun it again
> right away and it takes 2.5 hours.
> I run a statistics IO trace, and the only difference is the cumulative
> wait time...which is much much higher when the backup takes 2.5 hours.
> Any suggestions on how to troubleshoot this? Or why it might be
> happening?
Here are some ideas...
E: to F: - are these different drives on a san and does the backup
have to go accross the network - higher network traffic at times?
Network saturation?
Are their heavier user processes at times? Heavy duty queries/long
running transactions?
Is this a dedicated SQL Server, could another application be hogging
the Memory or IO?
As you can see, I am just shooting blind. Maybe some of these ideas
can help you think how to investigate?

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!

How can I track the queries being issued against my sql server 2000 instance?

I have an ASP.NET app built on top of SQL Server 2000. My app is running slowly and I think I'm issuing too many queries to the database.

How can I track the queries, and when they are being issued, against my sql server 2000 instance? Is there a tool to view the queries, or is it all in a log file somewhere?

This will help me tune my ASP.NET caching strategy.

Any help is greatly appreciated!

Franco

Use SQL Server Profiler|||Choose: SQL Server, Tools, SQL Profile, File, New, Trace, Choose Server...

On the filters tab of the new window you see you can choose various filters. These include database name, application name, NT UserName, there are many to choose from.

SQL Profiler is an amazing tool for seeing whats happening "under the hood" I suggest you really read up on it in SQL Books Online as its extremely powerful.

Keep us all up to date with how you are getting on.

hth

Pace
|||Fantastic... just what I needed. Thanks!