data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive/2006/11/30/who-needs-encryption.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is written
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is know
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

data encryption in SQL Server 2005 - protect from SQL Admnis

I need to store some sensitive data in SQL 2005.
Stored procedures will encrypt & decrypt the data. The client app is written
in .NEt using a specific user (belonging to a specific - custom role).
However, inspite of the above, the local Admin can always view the code in
the decription stored procedure & decrypt & hence view the data.
How can i prevent the administrator (everyone) except for the application
from being able to view the data.
Is it possible to remove access to a stored procedure even from an
administrator & give access to a special user (the password of which is know
only by the application)'
Then again the owner of the above role will have access to the stored
procedures!!This is a good backgrounder on the topic:
http://blogs.msdn.com/lcris/archive...encryption.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don" <Don@.discussions.microsoft.com> wrote in message
news:A25E337B-AA5C-456B-95AD-E4D2F36D4B0A@.microsoft.com...
>I need to store some sensitive data in SQL 2005.
> Stored procedures will encrypt & decrypt the data. The client app is writt
en
> in .NEt using a specific user (belonging to a specific - custom role).
> However, inspite of the above, the local Admin can always view the code in
> the decription stored procedure & decrypt & hence view the data.
> How can i prevent the administrator (everyone) except for the application
> from being able to view the data.
> Is it possible to remove access to a stored procedure even from an
> administrator & give access to a special user (the password of which is kn
ow
> only by the application)'
> Then again the owner of the above role will have access to the stored
> procedures!!

Data Conversion Errors on Excel Import into existing table

Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.

Any help would be appreciated.

More information needed.

Why is it failing? What does the error message say?

You will need to open up the package and edit it to do the conversions that you require.

-Jamie

Data conversion

Hi,
Usually how people do the data conversion? For example, I have a well
defined database, the new client use another similar relations database and
have few years data in it, he want to migrate to my system, so I need to
convert his data into my database, customer, appointment, transactions,
etc.)
Is it a way to handle this? Please advice.js wrote:
> Hi,
> Usually how people do the data conversion? For example, I have a well
> defined database, the new client use another similar relations
> database and have few years data in it, he want to migrate to my
> system, so I need to convert his data into my database, customer,
> appointment, transactions, etc.)
>
> Is it a way to handle this? Please advice.
If this is a one-off scenario, you need to scope out the migration with
a complete mapping of the source and destination databases and any
scrubbing that needs to occur to the data, design the migration code,
test it repeatedly on a test server and then run it in production when
it's ready. You can use DTS to help with the migration if the data
scrubbing is involved. If not, you may be able to do this using T-SQL.
David Gugick
Imceda Software
www.imceda.com|||Thanks David,
"David Gugick"
> js wrote:
> If this is a one-off scenario, you need to scope out the migration with a
> complete mapping of the source and destination databases and any scrubbing
> that needs to occur to the data, design the migration code, test it
> repeatedly on a test server and then run it in production when it's ready.
> You can use DTS to help with the migration if the data scrubbing is
> involved. If not, you may be able to do this using T-SQL.
>
Is it any articles available to learn?
This is not a one-off scenario (depend on the clients system) , the business
logics in general are deal the same, for example, custom info, order info,
but they can represent different in the field name and size
I did lots of data conversion use queries before. Just wander there are good
ways (or tools) out there can simplify the process?
Use DTS in my case is slow, I could use it wrong, because I always use it to
process the records by row to massage the data. Using T-SQL (deal with
large set of data), it is faster. Is it right?

Data communication compression?

Hi,
Do you know if there is any tool which can compress the trafic between a
client and an SQL server?
I really talk about communication compression NOT storage compression.
We have some remote sites with a low bandwidth and they have to upload some
data every week. its too slow today.
so if there is no option to compress the communication himself, I'll change
the strategy to export, zip, and copy the file. but this method add a layer
of complexity in the process so I prefer to not do this.
thanks for your guides.
Jerome.On Jun 11, 2:15 pm, "Jeje" <willg...@.hotmail.com> wrote:
> Hi,
> Do you know if there is any tool which can compress the trafic between a
> client and an SQL server?
> I really talk about communication compression NOT storage compression.
> We have some remote sites with a low bandwidth and they have to upload some
> data every week. its too slow today.
> so if there is no option to compress the communication himself, I'll change
> the strategy to export, zip, and copy the file. but this method add a layer
> of complexity in the process so I prefer to not do this.
> thanks for your guides.
> Jerome.
Hi Jerome
This would be something done at the lowest network layers as the
actual format of the packets needs to be the same for SQL Server to
understand them.
If you are doing a total data refresh then to cut down what you
transfer you may want to consider only distributing a differences. If
the data is rarely queries and response then maybe something like
using SOAP may be an alternative to your database.
John

Data Archiving...sounds easy but how to approach.

hell all,
What is the best way to achieve the following mechanism.

My product has a database, which will be installed at my client site.
as we all know database is something which will grow tremendously.
Now i am planning to come out with a Data archiving facility in such a way that
Yearly data will be backed up and will be maintained in a separate machine.
The data that was backed up will be removed from the current machine's database.Ofcourse
there will be a mchanism provided through which i can transfer data from offline database to online database.
Now, please throw u r thoughts on how to approach this problem...
My database is in SQL Server.

Thanks and regards
saiHi Sai -

I don't know if this is the best way to accomplish this task -

but i set up a job that runs each day at 4:30am (after backup is completed)

that simply deletes all records that are 365 days old in the tables
that i want to keep trimmed -

I do have a datetime stamp on each table that is populated when the record is added to the table -

hope this helps -

take care
tony

Data Access to SQL Server 2005 from remote computer.

We have a program (Hindsite) that we're using on our LAN. It uses ODBC to access data on the server from client PC. Hindsite directory on the server is mapped drive on each client in the office.

We would really like to be able to access the same program database on the server from the remote PC over the internet.

Can SQL Native Client do that?

What would be the best way to accomplish it?

How to make this application to be able to access it's directory on the server over the Internet like it was a mapped drive on the remote PC?

Thank you very much in advance.

SQL native can do that.

You need to make sure your server is accessible from remote site, either via direct internet connection or via "middlebox", such as NAT. As long as you can ping and telnet to your server machine from the remote site, you can use SQL native client to connect to your sql server.

|||

thank you very much for your advice.

I'll try that, hopefully it works.

one thing I don't understand - when the program asks for a directory on server what do I need to direct it to when I install it on the remote computer?

thank you again.

|||Can you be more specific.

Dangers of Client batch cancellation

After reading Erland Sommarskog's most enlightening articles on SQL Server
2000's error handling capabilities
(http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html) I have a question regarding
batch cancellation caused by a client.

Because a batch cancel request from a client does not cause a rollback, is
there any danger of the commands that were executed in the batch before the
client cancelled to be persistent in the database since no commit is ever
called? Or is the real problem indefinite locking that only disappears when
the client connection is terminated?

Any insights are appreciated!

Regards,

Tyler
Tyler Hudson wrote:

> After reading Erland Sommarskog's most enlightening articles on SQL Server
> 2000's error handling capabilities
> (http://www.sommarskog.se/error-handling-I.html and
> http://www.sommarskog.se/error-handling-II.html) I have a question regarding
> batch cancellation caused by a client.
> Because a batch cancel request from a client does not cause a rollback, is
> there any danger of the commands that were executed in the batch before the
> client cancelled to be persistent in the database since no commit is ever
> called? Or is the real problem indefinite locking that only disappears when
> the client connection is terminated?
> Any insights are appreciated!

If a batch is being executed and you do a cancel, everything done before the
cancel is acknowledged and executed is still done. If a tx was underway, it still
is, and the updates and locks are still in effect until you commit or rollback,
at which time everything that was done during the tx and all locks obtained,
are released.
If a batch is executed in a non-transactional mode, I believe that everything
done by the batch is permanently done. No locks remain after the cancel.
Joe Weinstein at BEA
>
> Regards,
>
> Tyler|||So let's say the client disconnects after issuing the cancel command. Would
SQL server roll back the commands executed before the cancel command?

"Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
news:40F6AC1A.3040303@.bea.com...
>
> Tyler Hudson wrote:
> > After reading Erland Sommarskog's most enlightening articles on SQL
Server
> > 2000's error handling capabilities
> > (http://www.sommarskog.se/error-handling-I.html and
> > http://www.sommarskog.se/error-handling-II.html) I have a question
regarding
> > batch cancellation caused by a client.
> > Because a batch cancel request from a client does not cause a rollback,
is
> > there any danger of the commands that were executed in the batch before
the
> > client cancelled to be persistent in the database since no commit is
ever
> > called? Or is the real problem indefinite locking that only disappears
when
> > the client connection is terminated?
> > Any insights are appreciated!
> If a batch is being executed and you do a cancel, everything done before
the
> cancel is acknowledged and executed is still done. If a tx was underway,
it still
> is, and the updates and locks are still in effect until you commit or
rollback,
> at which time everything that was done during the tx and all locks
obtained,
> are released.
> If a batch is executed in a non-transactional mode, I believe that
everything
> done by the batch is permanently done. No locks remain after the cancel.
> Joe Weinstein at BEA
> > Regards,
> > Tyler|||
Tyler Hudson wrote:

> So let's say the client disconnects after issuing the cancel command. Would
> SQL server roll back the commands executed before the cancel command?

If the connection was running in a transactional state, then the DBMS would find the
client had gone away, and would roll back the tx and anything it had done,
whether the cancel had been sent or not, though depending on how big the batch
was, the cancel might save some time.
If the connection was not in a transactional state, whatever was done before the
cancel or disconnect stays done.
Joe

>
> "Joe Weinstein" <joeNOSPAM@.bea.com> wrote in message
> news:40F6AC1A.3040303@.bea.com...
>>
>>Tyler Hudson wrote:
>>
>>
>>>After reading Erland Sommarskog's most enlightening articles on SQL
> Server
>>>2000's error handling capabilities
>>>(http://www.sommarskog.se/error-handling-I.html and
>>>http://www.sommarskog.se/error-handling-II.html) I have a question
> regarding
>>>batch cancellation caused by a client.
>>>
>>>Because a batch cancel request from a client does not cause a rollback,
> is
>>>there any danger of the commands that were executed in the batch before
> the
>>>client cancelled to be persistent in the database since no commit is
> ever
>>>called? Or is the real problem indefinite locking that only disappears
> when
>>>the client connection is terminated?
>>>
>>>Any insights are appreciated!
>>
>>If a batch is being executed and you do a cancel, everything done before
> the
>>cancel is acknowledged and executed is still done. If a tx was underway,
> it still
>>is, and the updates and locks are still in effect until you commit or
> rollback,
>>at which time everything that was done during the tx and all locks
> obtained,
>>are released.
>> If a batch is executed in a non-transactional mode, I believe that
> everything
>>done by the batch is permanently done. No locks remain after the cancel.
>>Joe Weinstein at BEA
>>
>>>
>>>Regards,
>>>
>>>
>>>Tyler
>>>
>>>
>>|||Tyler Hudson (TylerH@.Spam.MeNOTallpax.com) writes:
> Because a batch cancel request from a client does not cause a rollback,
> is there any danger of the commands that were executed in the batch
> before the client cancelled to be persistent in the database since no
> commit is ever called? Or is the real problem indefinite locking that
> only disappears when the client connection is terminated?

The most likely problem is the indefinite locking. But of course you
could run into data being persisted that you did not intend to persist.
Assume that further afield you have another error where you incorrectly
commit one time too much.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Tyler Hudson (TylerH@.Spam.MeNOTallpax.com) writes:
> So let's say the client disconnects after issuing the cancel command.
> Would SQL server roll back the commands executed before the cancel
> command?

Uncommitted transactions would be rolled back. But anything that already
has been committed will of course not be rolled back. Say that the user
submits a call to a stored procedures that performs this:

BEGIN TRANSACTION A
-- do some stuff
COMMIT TRANSACTION A

BEGIN TRANSACTION B
-- do some stuff
COMMIT TRANSACTION B

UPDATE tbl SET ...

BEGIN TRANSACTION C
-- do some stuff
BEGIN TRANSACTION D
-- do some other stuff
COMMIT TRANSACTION D
-- do even more stuff
COMMIT TRANSACTION C

Say now that the timeout sets in while transaction C is in progress, and
the client then disconnects. Transactions A, B and the UPDATE statement
will remain, but there will be nothing of transaction C. Note that this
applies even if COMMIT TRANSACTION D has been committed, because COMMIT
for a nested transaction only decrements the transaction level. Nothing
is committed.

If the timeout sets in while the UPDATE command is running, transaction
A and B will remain, but there will be no trace of the UPDATE statement.
(And of course not of transactions C and D.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

CXPACKET Wait Issue

I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest. :(
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:
> I was monitoring a client's warehouse build process last night and stumbled
> across the following. During a sproc call that moves data from a staging db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there with
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get more
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest. :(
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least not
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>> I was monitoring a client's warehouse build process last night and
>> stumbled
>> across the following. During a sproc call that moves data from a staging
>> db
>> to warehouse db, the spid parallelized on all 8 cpus and then sat there
>> with
>> the following information shown by sys.dm_os_waiting_tasks. Note that
>> all
>> CPUs were pegged at 100% during this event, although I don't know if this
>> particular proc was the cause. To my knowledge this was the only
>> activity
>> on the server at the time however. There wasn't really any other waiting
>> tasks of note other than the typical system tasks. I was unable to get
>> more
>> detailed information on the actual blocking tasks tho. It was pretty
>> late
>> in the night and I wasn't my sharpest. :(
>> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
>> 4
>> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
>> (7
>> drive RAID5 set on a middling SAN) and the database totals about 400GB or
>> so.
>>
>> There were 8 rows in waiting tasks with the below data:
>> waiting_task_address: 0x0000000000EDA868
>> session_id: 57
>> wait_duration_ms: 193860 (at the time of the DMV grab)
>> wait_type: CXPACKET
>> resource_address: 0x00000000801F9A50
>> blocking_task_address: all were different
>> blocking_session_id: 57
>> blocking_exec_context_id: all were different
>> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>>
>> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> resource_description is or means? Is it simply an internal mechanism
>> related to parallelization and perhaps this query (it was either a huge
>> insert or update - not sure which, sorry) could be sped up (or at least
>> not
>> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> maybe
>> 4? Any information or help would be appreciated.
>>
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:
> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > Hi
> >
> > Have you checked your missing indexes for anything related to the task?
> >
> > John
> >
> > "TheSQLGuru" wrote:
> >
> >> I was monitoring a client's warehouse build process last night and
> >> stumbled
> >> across the following. During a sproc call that moves data from a staging
> >> db
> >> to warehouse db, the spid parallelized on all 8 cpus and then sat there
> >> with
> >> the following information shown by sys.dm_os_waiting_tasks. Note that
> >> all
> >> CPUs were pegged at 100% during this event, although I don't know if this
> >> particular proc was the cause. To my knowledge this was the only
> >> activity
> >> on the server at the time however. There wasn't really any other waiting
> >> tasks of note other than the typical system tasks. I was unable to get
> >> more
> >> detailed information on the actual blocking tasks tho. It was pretty
> >> late
> >> in the night and I wasn't my sharpest. :(
> >>
> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM,
> >> 4
> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy
> >> (7
> >> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> >> so.
> >>
> >>
> >> There were 8 rows in waiting tasks with the below data:
> >>
> >> waiting_task_address: 0x0000000000EDA868
> >> session_id: 57
> >> wait_duration_ms: 193860 (at the time of the DMV grab)
> >> wait_type: CXPACKET
> >> resource_address: 0x00000000801F9A50
> >> blocking_task_address: all were different
> >> blocking_session_id: 57
> >> blocking_exec_context_id: all were different
> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
> >>
> >>
> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> >> resource_description is or means? Is it simply an internal mechanism
> >> related to parallelization and perhaps this query (it was either a huge
> >> insert or update - not sure which, sorry) could be sped up (or at least
> >> not
> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
> >> maybe
> >> 4? Any information or help would be appreciated.
> >>
> >>
> >> --
> >> Kevin G. Boles
> >> TheSQLGuru
> >> Indicium Resources, Inc.
> >> kgboles a earthlink dt net
> >>
> >>
> >>
> >>
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
> > Unfortunately the client rebuilds all indexes each night with their
> > warehouse rebuild so missing indexes isn't useful.
> > --
> > Kevin G. Boles
> > TheSQLGuru
> > Indicium Resources, Inc.
> > kgboles a earthlink dt net
> > "John Bell" <jbellnewspo...@.hotmail.com> wrote in message
> >news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
> > > Hi
> > > Have you checked your missing indexes for anything related to the task=?
> > > John
> > > "TheSQLGuru" wrote:
> > >> I was monitoring a client's warehouse build process last night and
> > >> stumbled
> > >> across the following. =A0During a sproc call that moves data from a s=taging
> > >> db
> > >> to warehouse db, the spid parallelized on all 8 cpus and then sat the=re
> > >> with
> > >> the following information shown by sys.dm_os_waiting_tasks. =A0Note t=hat
> > >> all
> > >> CPUs were pegged at 100% during this event, although I don't know if =this
> > >> particular proc was the cause. =A0To my knowledge this was the only
> > >> activity
> > >> on the server at the time however. =A0There wasn't really any other w=aiting
> > >> tasks of note other than the typical system tasks. =A0I was unable to= get
> > >> more
> > >> detailed information on the actual blocking tasks tho. =A0It was pret=ty
> > >> late
> > >> in the night and I wasn't my sharpest. =A0:(
> > >> This is a 2005 box, patched up past SP2 to build 3186. =A064bit, 32GB= RAM,
> > >> 4
> > >> dual core sprocs no hyperthreading. =A0The I/O subsystem isn't very b=eefy
> > >> (7
> > >> drive RAID5 set on a middling SAN) and the database totals about 400G=B or
> > >> so.
> > >> There were 8 rows in waiting tasks with the below data:
> > >> waiting_task_address: =A00x0000000000EDA868
> > >> session_id: =A057
> > >> wait_duration_ms: =A0 =A0 =A0193860 (at the time of the DMV grab)
> > >> wait_type: =A0CXPACKET
> > >> resource_address: =A0 0x00000000801F9A50
> > >> blocking_task_address: =A0all were different
> > >> blocking_session_id: =A057
> > >> blocking_exec_context_id: =A0all were different
> > >> resource_description: =A0exchangeEvent id=3Dport801f61c0 nodeId=3D5
> > >> Anyone got any ideas what an "exchangeEvent id=3Dport801f61c0 nodeId==3D5"
> > >> resource_description is or means? =A0Is it simply an internal mechani=sm
> > >> related to parallelization and perhaps this query (it was either a hu=ge
> > >> insert or update - not sure which, sorry) could be sped up (or at lea=st
> > >> not
> > >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or=
> > >> maybe
> > >> 4? =A0Any information or help would be appreciated.
> > >> --
> > >> Kevin G. Boles
> > >> TheSQLGuru
> > >> Indicium Resources, Inc.
> > >> kgboles a earthlink dt net- Hide quoted text -
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about 400GB
>> >> or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive/2006/09/25/deadlock-troubleshooting-part-3.aspx
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>> Hi
>> A common cause of parellisation is missing indexes, so unless you can
>> find
>> the query that is probably causing this you may not be able to solve the
>> issue. You may want to try SQL profiling the process.
>> John
>> "TheSQLGuru" wrote:
>> Unfortunately the client rebuilds all indexes each night with their
>> warehouse rebuild so missing indexes isn't useful.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>> kgboles a earthlink dt net
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>> > Hi
>> >
>> > Have you checked your missing indexes for anything related to the
>> > task?
>> >
>> > John
>> >
>> > "TheSQLGuru" wrote:
>> >
>> >> I was monitoring a client's warehouse build process last night and
>> >> stumbled
>> >> across the following. During a sproc call that moves data from a
>> >> staging
>> >> db
>> >> to warehouse db, the spid parallelized on all 8 cpus and then sat
>> >> there
>> >> with
>> >> the following information shown by sys.dm_os_waiting_tasks. Note
>> >> that
>> >> all
>> >> CPUs were pegged at 100% during this event, although I don't know if
>> >> this
>> >> particular proc was the cause. To my knowledge this was the only
>> >> activity
>> >> on the server at the time however. There wasn't really any other
>> >> waiting
>> >> tasks of note other than the typical system tasks. I was unable to
>> >> get
>> >> more
>> >> detailed information on the actual blocking tasks tho. It was pretty
>> >> late
>> >> in the night and I wasn't my sharpest. :(
>> >>
>> >> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB
>> >> RAM,
>> >> 4
>> >> dual core sprocs no hyperthreading. The I/O subsystem isn't very
>> >> beefy
>> >> (7
>> >> drive RAID5 set on a middling SAN) and the database totals about
>> >> 400GB or
>> >> so.
>> >>
>> >>
>> >> There were 8 rows in waiting tasks with the below data:
>> >>
>> >> waiting_task_address: 0x0000000000EDA868
>> >> session_id: 57
>> >> wait_duration_ms: 193860 (at the time of the DMV grab)
>> >> wait_type: CXPACKET
>> >> resource_address: 0x00000000801F9A50
>> >> blocking_task_address: all were different
>> >> blocking_session_id: 57
>> >> blocking_exec_context_id: all were different
>> >> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>> >>
>> >>
>> >> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
>> >> resource_description is or means? Is it simply an internal mechanism
>> >> related to parallelization and perhaps this query (it was either a
>> >> huge
>> >> insert or update - not sure which, sorry) could be sped up (or at
>> >> least
>> >> not
>> >> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or
>> >> maybe
>> >> 4? Any information or help would be appreciated.
>> >>
>> >>
>> >> --
>> >> Kevin G. Boles
>> >> TheSQLGuru
>> >> Indicium Resources, Inc.
>> >> kgboles a earthlink dt net
>> >>
>> >>
>> >>
>> >>
>>
>

CXPACKET Wait Issue

I was monitoring a client's warehouse build process last night and stumbled
across the following. During a sproc call that moves data from a staging db
to warehouse db, the spid parallelized on all 8 cpus and then sat there with
the following information shown by sys.dm_os_waiting_tasks. Note that all
CPUs were pegged at 100% during this event, although I don't know if this
particular proc was the cause. To my knowledge this was the only activity
on the server at the time however. There wasn't really any other waiting
tasks of note other than the typical system tasks. I was unable to get more
detailed information on the actual blocking tasks tho. It was pretty late
in the night and I wasn't my sharpest.
This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
drive RAID5 set on a middling SAN) and the database totals about 400GB or
so.
There were 8 rows in waiting tasks with the below data:
waiting_task_address: 0x0000000000EDA868
session_id: 57
wait_duration_ms: 193860 (at the time of the DMV grab)
wait_type: CXPACKET
resource_address: 0x00000000801F9A50
blocking_task_address: all were different
blocking_session_id: 57
blocking_exec_context_id: all were different
resource_description: exchangeEvent id=port801f61c0 nodeId=5
Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
resource_description is or means? Is it simply an internal mechanism
related to parallelization and perhaps this query (it was either a huge
insert or update - not sure which, sorry) could be sped up (or at least not
get hung up for an extended period) by specifying a MAXDOP of 1, 2 or maybe
4? Any information or help would be appreciated.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt netHi
Have you checked your missing indexes for anything related to the task?
John
"TheSQLGuru" wrote:

> I was monitoring a client's warehouse build process last night and stumble
d
> across the following. During a sproc call that moves data from a staging
db
> to warehouse db, the spid parallelized on all 8 cpus and then sat there wi
th
> the following information shown by sys.dm_os_waiting_tasks. Note that all
> CPUs were pegged at 100% during this event, although I don't know if this
> particular proc was the cause. To my knowledge this was the only activity
> on the server at the time however. There wasn't really any other waiting
> tasks of note other than the typical system tasks. I was unable to get mo
re
> detailed information on the actual blocking tasks tho. It was pretty late
> in the night and I wasn't my sharpest.
> This is a 2005 box, patched up past SP2 to build 3186. 64bit, 32GB RAM, 4
> dual core sprocs no hyperthreading. The I/O subsystem isn't very beefy (7
> drive RAID5 set on a middling SAN) and the database totals about 400GB or
> so.
>
> There were 8 rows in waiting tasks with the below data:
> waiting_task_address: 0x0000000000EDA868
> session_id: 57
> wait_duration_ms: 193860 (at the time of the DMV grab)
> wait_type: CXPACKET
> resource_address: 0x00000000801F9A50
> blocking_task_address: all were different
> blocking_session_id: 57
> blocking_exec_context_id: all were different
> resource_description: exchangeEvent id=port801f61c0 nodeId=5
>
> Anyone got any ideas what an "exchangeEvent id=port801f61c0 nodeId=5"
> resource_description is or means? Is it simply an internal mechanism
> related to parallelization and perhaps this query (it was either a huge
> insert or update - not sure which, sorry) could be sped up (or at least no
t
> get hung up for an extended period) by specifying a MAXDOP of 1, 2 or mayb
e
> 4? Any information or help would be appreciated.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
>|||Unfortunately the client rebuilds all indexes each night with their
warehouse rebuild so missing indexes isn't useful.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...[vbcol=seagreen]
> Hi
> Have you checked your missing indexes for anything related to the task?
> John
> "TheSQLGuru" wrote:
>|||Hi
A common cause of parellisation is missing indexes, so unless you can find
the query that is probably causing this you may not be able to solve the
issue. You may want to try SQL profiling the process.
John
"TheSQLGuru" wrote:

> Unfortunately the client rebuilds all indexes each night with their
> warehouse rebuild so missing indexes isn't useful.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FCF33C96-720B-4F2C-96BD-2E2A8179CFA5@.microsoft.com...
>
>|||On Dec 24, 8:10=A0pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> A common cause of parellisation is missing indexes, so unless you can find=[/vbcol
]
[vbcol=seagreen]
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
>
> "TheSQLGuru" wrote:
>
>
>
?[vbcol=seagreen]
>
>
>
taging[vbcol=seagreen]
re[vbcol=seagreen]
hat[vbcol=seagreen]
this[vbcol=seagreen]
aiting[vbcol=seagreen]
get[vbcol=seagreen]
ty[vbcol=seagreen]
>
RAM,[vbcol=seagreen]
eefy[vbcol=seagreen]
B or[vbcol=seagreen]
>
>
>
=3D5"[vbcol=seagreen]
sm[vbcol=seagreen]
ge[vbcol=seagreen]
st[vbcol=seagreen]
[vbcol=seagreen]
>
> - Show quoted text -
Hello,
maybe the reason of the problem are the alter index statements for
rebuilding the indeces.
If that is the case you should run it with specifying a MAXDOP
statement.|||I was specifically asking about the "exchangeEvent id=port801f61c0 nodeId=5"
resource_description. I am trying to identify what that means or comes from
to see if there is anything I can do to affect it being the wait.
The query that caused this hits the entire table since it is a load
mechanism. Indexing will not help since a tablescan is more efficient in
such cases.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...[vbcol=seagreen]
> Hi
> A common cause of parellisation is missing indexes, so unless you can find
> the query that is probably causing this you may not be able to solve the
> issue. You may want to try SQL profiling the process.
> John
> "TheSQLGuru" wrote:
>|||TheSQLGuru,
It looks like Bart Duncan knows:
http://blogs.msdn.com/bartd/archive...-3.asp
x
If you read all way down to the end, in his last post Bart makes the
following comment: "You're right -- this is a parallel thread deadlock.
The key indicator of this is the fact that the resources involved in the
deadlock (see the "resource-list" section) are not lock resources; they are
"exchangeEvent" resources, instead."
He goes on to suggest how to catch what is happennig by a profiler trace.
Hope that is some help,
RLF
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13n5da066uv3rce@.corp.supernews.com...
>I was specifically asking about the "exchangeEvent id=port801f61c0
>nodeId=5" resource_description. I am trying to identify what that means or
>comes from to see if there is anything I can do to affect it being the
>wait.
> The query that caused this hits the entire table since it is a load
> mechanism. Indexing will not help since a tablescan is more efficient in
> such cases.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> kgboles a earthlink dt net
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:FF9951B2-2ACE-4A72-8996-53F36082D827@.microsoft.com...
>

Customizing Security

Hello,
I was wondering if its possible to have database user authenticate
against an external database or directory server? The client has a
centralized repository system that they would like all systems to
authenticate against. If this is feasible, can offer any suggestions
on how we can achieve this?
Thanks in advance
TomHi Tom,
Are you asking about central authentication using SQL Security or
Windows Authentication?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I have a similar situation and need to plan for centralized security around
Windows/AD. Furthermore I need to leverage the AD groups/roles in the
architecture. ANy insite or direction would me appriciated.
"Kevin McDonnell [MSFT]" wrote:

> Hi Tom,
> Are you asking about central authentication using SQL Security or
> Windows Authentication?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Previous post:
I have a similar situation and need to plan for centralized security around
Windows/AD. Furthermore I need to leverage the AD groups/roles in the
architecture. ANy insite or direction would me appriciated.
Reply;
We use Domain based groups or local groups to base our security on. We can
publish objects to
Active Directory, but our security is not tied to AD like Exchange 2000 for
example. We can authenticate via
Trusted Connections using NTLM or Kerberos. Kerberos based authentication
requires that the Server Principal Name is
set for SQL. See Books Online for the example.
Our Security Model is discussed here:
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.