Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enable
d
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enabled
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?
Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Enabling TCP/IP protocol in SQL Server 6.5

I am using SQL Server 6.5. By default TCP/IP protocol is not enabled (1433 port is not opened by the server). How can I enable this so that I can use a JDBC driver to connect to it.

In SQL Server 2000, the "SQL Server Network Utility" helps us to enable the TCP/IP protocol. But not able to figure our how/where this can be done in 6.5.

Thanks for any helpGoto start --> programs --> MSSQL SErver 6.5 --> SQL Setup and from there you can add the network library for TCP/IP and then after you must restart SQL services to take affect of new settings.

Enabling Drill thru in Excel Pivot Table

Am I mistaken, or are we not able to share details contained in the fact table with our excel pivot table users?

I have enabled drill through in Analysis Services and it works terrifically from that console...how can I deliver the same detail content to my pivot table users? If not in a pivot table, what is recommended that can easily be integrated with the pivot tables?No you cannot drillthrough from a pivot table but microsoft was nice enough to give you code to enable a drillthrough. (Although I havn't tested it on fist glance I personally would modigy some of it).

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlextendolap.asp|||You're assuming I'm capable. Despite my inadequacy, I will see what I can manage.|||PS - Thanks for the heads-up. It's appreciated.|||Ok, after I tested it I decided against it.
It only works with office XP and an addin.
A link to the AddIn in found on the same page i gave you.
I you have office XP and this works, let me know.
Although this is a pathetic reason to upgrade a office of 300 people to office XP.
Sorry for Bitchin just letting off some steam.

enabled/disabled protocols for an msde instance

Hi folks,
Wondering if someone could help me out with an msde/windows sp2 problem.
Upon installing sp2, the network protocols for my msde instance become
disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can use to
re-enable these network protocols? Or is there a windows interface to enable
them? basically my msde instance is now standalone, no other workstations
can talk to that sql instance. I can't install sp3 for msde because the
manufcaturer used merge modules (bleh!).
Any help would be greatly appreciated.
Thanks,
S. Purkiss
sheldon@.REMOVETHISthedoctorcompany.com
hi,
"S Purkiss" <shel@.shel.com> ha scritto nel messaggio
news:w7idnY9fmfsFnOLcRVn-rA@.rogers.com
> Hi folks,
> Wondering if someone could help me out with an msde/windows sp2
> problem.
> Upon installing sp2, the network protocols for my msde instance become
> disabled: Sm, Np, Tcp, Via. Is there a command line syntax I can
> use to re-enable these network protocols? Or is there a windows
> interface to enable them? basically my msde instance is now
> standalone, no other workstations can talk to that sql instance. I
> can't install sp3 for msde because the manufcaturer used merge
> modules (bleh!).
> Any help would be greatly appreciated.
please run Server Network Utility (svrnetcn.exe) and re-enable the desired
protocol...
with MSDE sp2 level you are exposed at Slammer/Saphire troubles, which have
ben solved with service pack 3 - 3/a..
lot of security problems has been addressed to with that sp, so please
"force" your ISV to ship sp3, as it's been out for quite 1 year, and it
should already had enougth time to test and ship it...
my $0.02
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Enable/Disable of Toogle

I we a matrix in my report, I can set the 'Visibility can be toggled by' to
a item. and the toogled ability is enabled.
.
Can I set if the group an can be toogled according some custom code?Hi
Unless you are using dynamic SQL then you will need to return the same
columns back from your query and use the client to display/hide the column
e.g.
SELECT CASE @.isvisible THEN col1 ELSE '' END AS col1,
col2,
CASE @.isvisible THEN col3 ELSE '' END AS col3
FROM MyTable
John
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OwKw5DAzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>I we a matrix in my report, I can set the 'Visibility can be toggled by'
>to a item. and the toogled ability is enabled.
> .
> Can I set if the group an can be toogled according some custom code?
>

Enable/Disable of Toogle

I we a matrix in my report, I can set the 'Visibility can be toggled by' to
a item. and the toogled ability is enabled.
.
Can I set if the group an can be toogled according some custom code?Hi
Unless you are using dynamic SQL then you will need to return the same
columns back from your query and use the client to display/hide the column
e.g.
SELECT CASE @.isvisible THEN col1 ELSE '' END AS col1,
col2,
CASE @.isvisible THEN col3 ELSE '' END AS col3
FROM MyTable
John
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OwKw5DAzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>I we a matrix in my report, I can set the 'Visibility can be toggled by'
>to a item. and the toogled ability is enabled.
> .
> Can I set if the group an can be toogled according some custom code?
>

Enable/Disable of Toogle

I we a matrix in my report, I can set the 'Visibility can be toggled by' to
a item. and the toogled ability is enabled.
..
Can I set if the group an can be toogled according some custom code?
Hi
Unless you are using dynamic SQL then you will need to return the same
columns back from your query and use the client to display/hide the column
e.g.
SELECT CASE @.isvisible THEN col1 ELSE '' END AS col1,
col2,
CASE @.isvisible THEN col3 ELSE '' END AS col3
FROM MyTable
John
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OwKw5DAzFHA.3408@.TK2MSFTNGP09.phx.gbl...
>I we a matrix in my report, I can set the 'Visibility can be toggled by'
>to a item. and the toogled ability is enabled.
> .
> Can I set if the group an can be toogled according some custom code?
>

Enable the CLR at database level only

Hi,

Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.

Kind regards,

Jan.

CLR is enabled by instance, you could sepearate the database by instances whereas one would allow, the other one would t allow the CLR.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Enable Quoted Identifiers=0 System.Data.SqlClient.sqlConnection

Hi there,
this is probably a common question,
how can i include the "enabled Quoted Identifiers" settings in the
connection string of the sqlconnection.
on trawling through web pages i have identified
1 .that by default the quoted identifiers in SQL Server 2000 is by default
set to on
2. changing the status of quoted identifiers at the catalog level has no
affect at the connection level
since i have 1 global connection string
I would be grateful if anyone could help me in setting up this connection
stringHello,
As I know, you have to run SET QUOTED_IDENTIFIER { ON | OFF } via
connection and there is no such option to set this option in connection
string to SQL server.
Also, if you use odbc DSN to connect to SQL Server, you have the option to
configure this option in SQL Server DSN.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Enable Quoted Identifiers=0
System.Data.SqlClient.sqlConnection
>thread-index: AcZaMC8+PO+Q1I/hQvOGfgZaOBq/Mw==
>X-WBNR-Posting-Host: 83.141.82.45
>From: examnotes <cathiec@.newsgroups.nospam>
>Subject: Enable Quoted Identifiers=0 System.Data.SqlClient.sqlConnection
>Date: Fri, 7 Apr 2006 03:44:02 -0700
>Lines: 18
>Message-ID: <D8216A73-DDA7-417E-B53A-54889FDF94AF@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>Newsgroups: microsoft.public.sqlserver.connect
>Path: TK2MSFTNGXA01.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.connect:47316
>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>Hi there,
>this is probably a common question,
>how can i include the "enabled Quoted Identifiers" settings in the
>connection string of the sqlconnection.
>on trawling through web pages i have identified
>1 .that by default the quoted identifiers in SQL Server 2000 is by default
>set to on
>2. changing the status of quoted identifiers at the catalog level has no
>affect at the connection level
>since i have 1 global connection string
>I would be grateful if anyone could help me in setting up this connection
>string
>
>

Enable Production SQL Server Express for SQLCacheDependency?

Q1: Can I just open these protocols I need (see web links below) and then close them down again after SQLCacheDependency has been enabled? Note: The website (asp.net 2.0/SQL server Express) operates OK right now with asp.net - but it does not have SQLCacheDependency enabled.

Problem: When I try to do this I keep on getting this error message:

"An error has occurred when establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."

None of these 3 commands have worked (all give the same message above):

aspnet_regsql.exe -U user1 -P pwd1 -S server1/SQLEXPRESS -d myDBName -ed
aspnet_regsql.exe -E -S server1/SQLEXPRESS -d myDBName -ed
aspnet_regsql.exe -E -S .\SqlExpress -d myDBName -ed

I am trying to run these commands via a MSTSC connection. Q2: Is that (terminal server) the problem (the "remote connection") complained of above ?

This is the command that worked on my local dev machine:

aspnet_regsql.exe -E -S .\SqlExpress -d myDBName -ed

Q3: Must I really go through the procedure "How to: Configure Express to accept remote connections" at:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

I don't really want to have SQL server accepting any connections as the last time it was listening on these ports we had so many attacks being made that it was filling up the logs!

What I can't understand is that every night the database is backed up with the following command line code (operating as a Scheduled Task):

sqlcmd -s server1/SQLEXPRESS -i MW4BAK.SQL -E

Q4: So if the server is happy receiving commands from 'sqlcmd.exe' why does it have such problems with 'aspnet_regsql.exe'

Q5: If I grouped those statements I need to setup SQLCacheDependency under a Scheduled Task would they work (as there would presumably be no remote connection then) or is it something in the nature of the connection established which allows sqlcmd.exe to work but stops aspnet_regsql.exe?

I was able to configure the database for cache dependency by just running the command with no parameters and letting the wizard take over:

aspnet_regsql.exe

But ICAN NOT enable the tables e.g.
aspnet_regsql.exe -E -S server1\SqlExpress -d myDBName -t myTable1 -et
continues to give the error message above (implying that it won't allow a remote connection).

What's the problem? Is it because I'm running the command remotely under terminal server?

|||

Apologies. I sorted it out.

The SQL instance is not called "server1/SQLEXPRESS". It is only
"server1" (on this server). So the following will work.

aspnet_regsql.exe -E -S server1 -d myDBName -ed
aspnet_regsql.exe -E -S server1 -d myDBName -t myTableName -et

etc. for each table

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peter
hi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
current location: alicante (es)
"Peter Hartlén" wrote:

> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be in
> trouble if there is a power outage. But how serious is this? The machine is
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it when
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>
|||"Peter Hartln" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter
|||"Peter Hartln" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.

> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.

> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David
|||Hi David, thanks for your reply!

> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter
|||"Peter Hartln" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.

> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartln" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David
|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peterhi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
current location: alicante (es)
"Peter Hartlén" wrote:

> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
in
> trouble if there is a power outage. But how serious is this? The machine i
s
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it whe
n
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>|||"Peter Hartln" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter|||"Peter Hartln" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.

> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.

> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David|||Hi David, thanks for your reply!

> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter|||"Peter Hartln" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.

> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartln" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>

enable advanced performance - no UPS

Hi!
I just enabled disk cache and advanced performance on our SQL-server
machine's disks. The computer doesn't have any UPS, so I guess I could be in
trouble if there is a power outage. But how serious is this? The machine is
a dedicated sql server which mainly serves as a development machine. We do
however store some license information in the sql-server, that database is
however backed up on a regular basis.
Which are the worst case scenario?
- Re-install Win. Serv. 2003?
- Re-install SQL-server?
- Some corrupted/lost datarows in the database if someone worked on it when
the outage happened?
- Complete loss of all data?
- Unusable database files?
Regards,
Peterhi Peter,
I consider that "complete loss of all data" is the worst situation for
anyone, but i was thinking about UPS...Listen to me, even in Spain, lots of
organizations own an UPS, I can't believe it!! It's cheaper, isn't?
--
current location: alicante (es)
"Peter Hartlén" wrote:
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be in
> trouble if there is a power outage. But how serious is this? The machine is
> a dedicated sql server which mainly serves as a development machine. We do
> however store some license information in the sql-server, that database is
> however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it when
> the outage happened?
> - Complete loss of all data?
> - Unusable database files?
> Regards,
> Peter
>
>|||"Peter Hartlén" <peter@.data.se> wrote in message
news:OE9flvDSGHA.4384@.tk2msftngp13.phx.gbl...
> Hi!
> I just enabled disk cache and advanced performance on our SQL-server
> machine's disks. The computer doesn't have any UPS, so I guess I could be
> in trouble if there is a power outage. But how serious is this? The
> machine is a dedicated sql server which mainly serves as a development
> machine. We do however store some license information in the sql-server,
> that database is however backed up on a regular basis.
> Which are the worst case scenario?
> - Re-install Win. Serv. 2003?
> - Re-install SQL-server?
> - Some corrupted/lost datarows in the database if someone worked on it
> when the outage happened?
> - Complete loss of all data?
> - Unusable database files?
>
Realistically, the worst case is that you will need to recover your
databsaes from backup.
However, you should NEVER do this either in production or development. The
reasons for doing it in production are obvious.
In development turning on write cashing on disks badly distorts the
performance characteristics of the databsae by eliminating the cost of log
flushing. This can leave perforance problems to arise in production when
you wonder why trying to do 2000 single-row inserts without a transaction is
slow.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
> Realistically, the worst case is that you will need to recover your
> databsaes from backup.
> However, you should NEVER do this either in production or development.
> The reasons for doing it in production are obvious.
> In development turning on write cashing on disks badly distorts the
> performance characteristics of the databsae by eliminating the cost of log
> flushing. This can leave perforance problems to arise in production when
> you wonder why trying to do 2000 single-row inserts without a transaction
> is slow.
> David
I'm not sure I follow, you say "caching distorts the performance
characteristics of the database", but a 1.5min compared to 12min import
(almost 10 times slower) quite clearly indicates that there is a huge
performance benefit using caching.
Are you saying that after a while, the performance will decrease when using
caching because it messes up the log flushing? Are you even saying caching
invalidates the transaction log characteristics of a database?
Best regards,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:uqUda1ZSGHA.1688@.TK2MSFTNGP11.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
> meddelandet news:Oj8LdKGSGHA.4920@.tk2msftngp13.phx.gbl...
>> Realistically, the worst case is that you will need to recover your
>> databsaes from backup.
>> However, you should NEVER do this either in production or development.
>> The reasons for doing it in production are obvious.
>> In development turning on write cashing on disks badly distorts the
>> performance characteristics of the databsae by eliminating the cost of
>> log flushing. This can leave perforance problems to arise in production
>> when you wonder why trying to do 2000 single-row inserts without a
>> transaction is slow.
>> David
> I'm not sure I follow, you say "caching distorts the performance
> characteristics of the database", but a 1.5min compared to 12min import
> (almost 10 times slower) quite clearly indicates that there is a huge
> performance benefit using caching.
No that's a design flaw in your application. See below.
> Are you saying that after a while, the performance will decrease when
> using caching because it messes up the log flushing?
No.
> Are you even saying caching invalidates the transaction log
> characteristics of a database?
Yes. And you generally will not be able to use such caching in production.
So you shouldn't in development.
If you see a 10x difference with write caching, you probably have an
application problem. The most common such problem is flushing the
transaction log too often. You can only flush the log so many times a
second. If you insist on flushing the log after each row of the import
(which is what happens when you don't use a transaction), then you will
severly limit the throughput of your application.
You might well miss this design flaw on a development system which has write
caching enabled.
David|||Hi David, thanks for your reply!
> If you see a 10x difference with write caching, you probably have an
> application problem. The most common such problem is flushing the
> transaction log too often. You can only flush the log so many times a
> second. If you insist on flushing the log after each row of the import
> (which is what happens when you don't use a transaction), then you will
> severly limit the throughput of your application.
> You might well miss this design flaw on a development system which has
> write caching enabled.
>
I am not an expert when it comes to writing transactional database code, but
I think I have a fairly good grip on the basic functionality.
I know my development machine (using MSDE) has write cache, but our
testserver didn't. My code, starts a transaction at the beginning of the
import and commits it at the end of the transaction (not using any nested
transactions, should I?), unless something went wrong.
I am not sure what you mean by flushing the log, I only start a transaction
and commit it.
The test was performed on the testserver, without, and later on with, write
cache enabled. The code never changed, nor did the hardware itself, only the
write cache, and I got a 10x improvment when enabling write cache.
Perhaps my "single transaction of the entire import" is bad practice, should
I use one large and many smaller transactions during the import?
Are you saying write cache shouldn't impose a 10x performance benefit on a
SQL-server?
Thanks,
Peter|||"Peter Hartlén" <peter@.data.se> wrote in message
news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
> I am not an expert when it comes to writing transactional database code,
> but I think I have a fairly good grip on the basic functionality.
> I know my development machine (using MSDE) has write cache, but our
> testserver didn't. My code, starts a transaction at the beginning of the
> import and commits it at the end of the transaction (not using any nested
> transactions, should I?), unless something went wrong.
> I am not sure what you mean by flushing the log, I only start a
> transaction and commit it.
> The test was performed on the testserver, without, and later on with,
> write cache enabled. The code never changed, nor did the hardware itself,
> only the write cache, and I got a 10x improvment when enabling write
> cache.
> Perhaps my "single transaction of the entire import" is bad practice,
> should I use one large and many smaller transactions during the import?
No. One single transaction is just right. The mistake most people make is
omiting the transaction, and letting each statement commit by itself.
> Are you saying write cache shouldn't impose a 10x performance benefit on a
> SQL-server?
Yes. The only time write caching should makes a huge difference is when the
application is flushing the log (commiting transactions) too often.
What sort of performance numbers are you seeing? How are you doing the
import.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
> "Peter Hartlén" <peter@.data.se> wrote in message
> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any nested
>> transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware itself,
>> only the write cache, and I got a 10x improvment when enabling write
>> cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
> No. One single transaction is just right. The mistake most people make
> is omiting the transaction, and letting each statement commit by itself.
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
> Yes. The only time write caching should makes a huge difference is when
> the application is flushing the log (commiting transactions) too often.
> What sort of performance numbers are you seeing? How are you doing the
> import.
>
Let me explain a bit more.
When when a user needs to make a large number of changes to a database, as
in an import, there are four different places the changes have to be made:
The database pages in memory, the database files, the log records in memory
and the log file. The changes are made immediately to the database pages in
memory and the log records in memory. Background processes will then write
(or "flush") the changes to the files on disk. When you commit a
transaction, you must wait for any changes made to the log records in memory
to be flushed to disk.
If you are commiting after every statement, which is what happens if you
don't use an explicit transaction, then you must wait for the log file to be
written after each statement. This is the typical case where using write
caching on the disk will make a big difference.
If you do use a transaction, then the changes to the database and log in
memory are written out by background processes. These background processes
don't really benefit from write caching on the disks since they are writing
out large amounts of data. So if you have good transaction scoping, I am
surprised that you see a big performance difference with write caching
enabled.
David|||Do I fill stupid now or what, I was so sure I hade the file read operation
as a transaction, but it was the second operation, when updating the main
tables with the data in the temporary tables.
Adding transaction to the file read operation improved the import time to
near 2min compared to 12min.
Thanks a lot for you patience and explanations David!
Regards,
Peter
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> skrev i
meddelandet news:e5NXJMdTGHA.5884@.TK2MSFTNGP14.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:%23EZWYAdTGHA.736@.TK2MSFTNGP12.phx.gbl...
>> "Peter Hartlén" <peter@.data.se> wrote in message
>> news:eOqfoXZTGHA.5500@.TK2MSFTNGP12.phx.gbl...
>> Hi David, thanks for your reply!
>> If you see a 10x difference with write caching, you probably have an
>> application problem. The most common such problem is flushing the
>> transaction log too often. You can only flush the log so many times a
>> second. If you insist on flushing the log after each row of the import
>> (which is what happens when you don't use a transaction), then you will
>> severly limit the throughput of your application.
>> You might well miss this design flaw on a development system which has
>> write caching enabled.
>>
>> I am not an expert when it comes to writing transactional database code,
>> but I think I have a fairly good grip on the basic functionality.
>> I know my development machine (using MSDE) has write cache, but our
>> testserver didn't. My code, starts a transaction at the beginning of the
>> import and commits it at the end of the transaction (not using any
>> nested transactions, should I?), unless something went wrong.
>> I am not sure what you mean by flushing the log, I only start a
>> transaction and commit it.
>> The test was performed on the testserver, without, and later on with,
>> write cache enabled. The code never changed, nor did the hardware
>> itself, only the write cache, and I got a 10x improvment when enabling
>> write cache.
>> Perhaps my "single transaction of the entire import" is bad practice,
>> should I use one large and many smaller transactions during the import?
>> No. One single transaction is just right. The mistake most people make
>> is omiting the transaction, and letting each statement commit by itself.
>>
>> Are you saying write cache shouldn't impose a 10x performance benefit on
>> a SQL-server?
>> Yes. The only time write caching should makes a huge difference is when
>> the application is flushing the log (commiting transactions) too often.
>> What sort of performance numbers are you seeing? How are you doing the
>> import.
>
> Let me explain a bit more.
> When when a user needs to make a large number of changes to a database, as
> in an import, there are four different places the changes have to be made:
> The database pages in memory, the database files, the log records in
> memory and the log file. The changes are made immediately to the database
> pages in memory and the log records in memory. Background processes will
> then write (or "flush") the changes to the files on disk. When you commit
> a transaction, you must wait for any changes made to the log records in
> memory to be flushed to disk.
> If you are commiting after every statement, which is what happens if you
> don't use an explicit transaction, then you must wait for the log file to
> be written after each statement. This is the typical case where using
> write caching on the disk will make a big difference.
> If you do use a transaction, then the changes to the database and log in
> memory are written out by background processes. These background
> processes don't really benefit from write caching on the disks since they
> are writing out large amounts of data. So if you have good transaction
> scoping, I am surprised that you see a big performance difference with
> write caching enabled.
> David
>