Best Practice

We have a dedicated web server that runs SQL it is not hosted on our network
it is hosted by an ISP. We also run an inhouse SQL server. We need to get
info back and forth between the two DB's. What would be the best way to do
this securely.
Thank youA Scheduled DTS task. But, your table design needs to be able to handle such
a model.
You need a way to identify changed rows and a way to resolve them is they
were both changed between synchronization.
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/
"Ryan" <Ryan@.discussions.microsoft.com> wrote in message
news:4D58A22F-4269-4365-861C-DA7E202EFA04@.microsoft.com...
> We have a dedicated web server that runs SQL it is not hosted on our
> network
> it is hosted by an ISP. We also run an inhouse SQL server. We need to get
> info back and forth between the two DB's. What would be the best way to do
> this securely.
> Thank you|||Thank you for your reply one other question. Would the DTS package be secure
?
"Mike Epprecht (SQL MVP)" wrote:

> A Scheduled DTS task. But, your table design needs to be able to handle su
ch
> a model.
> You need a way to identify changed rows and a way to resolve them is they
> were both changed between synchronization.
> 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/
> "Ryan" <Ryan@.discussions.microsoft.com> wrote in message
> news:4D58A22F-4269-4365-861C-DA7E202EFA04@.microsoft.com...
>
>

Benefits of using SQL over XML

Hi,

I have a question relating to XML and SQL. My company currently runs a website which allows its clients to log in, view their accounts and transaction history online. The website is totally read only with the exception of changing passwords.

The data is taken from our back office system overnight which runs an oracle 8i database (we cannot like our website to the database due to the agreement we have in place with our software supplier). The data is written to a CSV file which is then converted into XML. The XML file is saved to the webserver and is referenced by the website.

The structure of the website has a relationship where the Client has a Manager who can see their clients accounts, a Branch level that can see all of their Managers and the underlying clients and then finally a company level that sees everything.

We are finding that using XML is causing a real issue in performance and I was wondering if migrating the website to SQL server would improve the performance of the queries etc .

Any advice would be gratefully recieved

Lee

It really depends on two things: The application and the version of SQL Server you are using. For certain input/retrieval methods, XML can actually be faster than using direct database calls. SQL Server 2005 has native XML features, which you can read more about here:

http://www.sqlsummit.com/People/MRys.htm

Buck

|||

The thing is that our website is taking considerably longer to return results using XML. Our software provider can provide a website which uses Oracle and an example website using test data seems to query and return the data back in far less time then ours using XML. But this site is a lot more costly option and does not provide all the functionalty we require. The main reason for the performance increase is that we want to be able to use the website internally for our branches and front office staff, so performance is key it will have about 20 - 30 users. We are planning to do this because we are unable to restrict access to parts of our back office system from the front office staff. The problem with the performance of the website currently means that the staff will have to deal with a sluggish system.

Our website designer has said that he would have to rewrite the website to change it from XML to SQL, would XQuery be a simpler solution. We are within reason happy to purchase whatever software is required to make this work.

|||

Again, it all depends on how the application is coded. Simply changing from XML to an RDBMS query doesn't guarentee that one will be faster than the other. In other words, you can code an application to be faster in either case.

If performance is key, then for large data sets a database platform might be the way to go. If you need to share data between multiple systems, then XML might be the way to go. It all depends on your needs, but in either case you'll want to evaluate your code to ensure that it is as optimal as possible for your situation.

being able to stop parallelism in a C++ Program calling SQL Server

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.

One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.

However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.

Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.

Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?

Thanks in Advance.

Peter Nolan

www.peternolan.com

select * from tablename OPTION (MAXDOP 1)

That should do it. MAXDOP stands for Max Degree of Parallelism, and allows you to limit the number of parallel execution threads for a query. Setting it to 1 essentially disables parallelism for that query.
|||

Hi David,

thanks very much for that....I did not know the syntax of how to do this at query level....

We turned off parallelism at the server level and it turns out the optimiser will still perform a table scan....we have many logical tables in on physical table and even when we query the underlying table with a select * from with a where clause it is scanning the table and taking about 40 seconds to do so....even when we say 'top 1' to make it get the first row and put an index on the field that logically partitions the table.

It looks like we will have to introduce partitioning so that the optimiser can know to scan just the partition in question. We also had a feature we planned to introduce which was to turn off table validation....if an etl subsystem is in production there is little need to validate tables....we never put it in...and now there seems like a need to do so.

Again, thanks for the tip. Much appreciated.

Peter

www.peternolan.com

|||

Hi All,

one of our guys here came up with a better idea....

What the tool is doing is validating the existence of the taget table/views passed to the program....so in the case of a typo a specific message saying the table/view not found is issued.

However the optimiser is thinking the program really wants all the rows.

One of the guys here suggested if the constraint where 1 = 0 is added to the select * then all optimisers will be smart enough to know no rows will be returned yet the database will still check that the table/view exists.

So we will also implement this test and that will make sure all programs test for existence of a table quickly.

If we feel industrious one day we can actually ask ODBC if the table exists.

One reason why I like the select * from tablename as a test for existence of a table over just asking ODBC is that it forces the preparation of the plan and if the view has been invalidated then it will also be caught at this stage of processing where ODBC will return a positive answer to does the view exist...

Again, thanks for your responses...it is much appreciated...

Just by the way....we installed the new performance dashboards just released and it was these that managed to show us the statement that was in a wait state and how we detected the problem...so if you have not installed the new performance dashboards yet....you might want to do so...

Best Regards

Peter

BEGIN TRANSACTION

have an application (c# net 1.1) that attaches to a MSDE 2000(fully patched)
machine the application runs for some time, but eventually enters a
begintransaction. it does not error, but it does not return from the call
either it just hangs.
have connected from another machine with enterprise manager and looked for
blocking processes etc but there are none.
It might run for hours, or a few minutes before the hang occurs
basically
write a log event
Begin transaction
write a log event.
all the code is in a try catch block, with logging, almost every line in the
procedure is now wrapped in a write a log event. it is hanging on the begin
transaction system, the connection is good.
hang in this case means forever, have to kill the application.
There are no other transactions running, and enterprise manager from a
remote machine can access the data when the application is hung.
Need some ideas as to what else to look for.
hi,
JR wrote:
> have an application (c# net 1.1) that attaches to a MSDE 2000(fully
> patched) machine the application runs for some time, but eventually
> enters a begintransaction. it does not error, but it does not return
> from the call either it just hangs.
> have connected from another machine with enterprise manager and
> looked for blocking processes etc but there are none.
> It might run for hours, or a few minutes before the hang occurs
> basically
> write a log event
> Begin transaction
> write a log event.
> all the code is in a try catch block, with logging, almost every line
> in the procedure is now wrapped in a write a log event. it is hanging
> on the begin transaction system, the connection is good.
> hang in this case means forever, have to kill the application.
> There are no other transactions running, and enterprise manager from a
> remote machine can access the data when the application is hung.
> Need some ideas as to what else to look for.
try having a look at
http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
if you can find some info about eventual pending transactions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea for your response
I have run DBCC OPENTRAN and no results are returned
Well actually the text "No active open transactions"
the call to begintransaction just locks and never returns
I really don't see anything blocking the begintransaction from working
Thanks
JR
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47b3h7FemenlU1@.individual.net...
> hi,
> JR wrote:
> try having a look at
> http://msdn.microsoft.com/library/de..._dbcc_5fhq.asp
> if you can find some info about eventual pending transactions..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> Thanks Andrea for your response
> I have run DBCC OPENTRAN and no results are returned
> Well actually the text "No active open transactions"
> the call to begintransaction just locks and never returns
> I really don't see anything blocking the begintransaction from working
yep, it just seems non transaction at all has been opend and your
application just gets stuck somewhere..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||yep, stuck in the .begintransaction so now what ?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47d6h2FerknmU1@.individual.net...
> hi,
> JR wrote:
> yep, it just seems non transaction at all has been opend and your
> application just gets stuck somewhere..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi,
JR wrote:
> yep, stuck in the .begintransaction so now what ?
perhaps in .net hierarchy in the language syntax..
if you can perform the included T-SQL command text without problem, I can
suppose the problem is elsewhere :-(
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You might want to turn on profiler to see if the begin transaction statement
is received and completes in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:47dj1jFeh0ueU1@.individual.net...
> hi,
> JR wrote:
> perhaps in .net hierarchy in the language syntax..
> if you can perform the included T-SQL command text without problem, I can
> suppose the problem is elsewhere :-(
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Thanks Roger
this is a good idea,
in the interim, have commented out the transaction altogether and
the code is running perfectly when it is not in a transaction.
Soon as the code uses a transaction it runs for a while and then hangs.
JR
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ewr$5IGRGHA.5108@.TK2MSFTNGP09.phx.gbl...
> You might want to turn on profiler to see if the begin transaction
> statement is received and completes in the database.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:47dj1jFeh0ueU1@.individual.net...
>