Wikser's questions

We're considering to enable our >10 suppliers (mainly from China) to input/edit/view data via an unified interface. The preferred way is to use the same windows-based .NET/MSSQL application as used internally.

As the application needs a direct database connection, my idea is to grant all suppliers access to a server at our site via terminal services. But there are concerns about the security of this approach, so that the current plan is to develop a web interface to the application (costly) or continue to use excel/txt/word/pdf for data submission (well...).

In deep detail, my approach would consist of:

external server

Windows Server 2003, SQL Server 2005, the application

• windows update set on auto

• Terminals Services, granted to individual users

• directly start application (group policy), auto log-off after termination

• adjust file system ACLs to prevent:

  • any file writes (except e.g. profile files)
  • execution of taskmgr, cmd, ftp, telnet, (explorer.exe?)

• registry ACLs should be ok

• change RDP port

• firewalled appropriately

• auditing enabled (group policy), report events via e-mail to admins:

  • execution of cmd, taskmgr, etc. (all users, including system/admin/...)
  • change of security permissions

internal server

• connected via crossover ethernet

• completely firewalled (inbound)

• synchronize every 5 minutes:

  • open LAN interface
  • synchronize files via network share
  • synchronize database via SQL Server replication (some tables inbound, many outbound)
  • close LAN interface

• backup and history backups (as the external server has the master copy of the applications database)

As I use a similar approach in my department (web- instead of terminal services), I have no objections to the setup. The only practicable attack I'm concerned with, is a breach of in the application itself, which could lead to a compromise of application specific data (over all suppliers).

Nevertheless, as I have to convince a bunch of people favouring the excel approach, I'd like to hear your thoughts.

Many thanks.

After 3 years of smooth operation, one of our servers (Windows Server 2003) started to fail at random times. No authentication, remote login or net share accesses are then possible.

Taking a look at the services list, many of the basic services such as the net logon, computer browser and workstation service are stopped. After restarting the services, everything seems to be working. Also, after a server restart, everything is working correctly for some days.

I can't think of any change that could've caused this. The server is fully updated, no viruses were found.

As there are no relevant entries in the error logs before the failure, I can't think of a quick way to locate the cause of the error.

Is it possible to log service crashs to the event log and restart the failing services automatically? What could be the cause of the malfunctions?

Thanks!

Today I roughly measured the benefits of enabling write-back on the RAID controller on a server at work. It got no RAID battery-backup-unit (BBU) so the write-cache is currently disabled. As the server is not used to capacity (by far), the results in most test were spectacular, e.g.:

• Database CRUD: before 35s, after 4s
• Saving a 1MB Excel file: before: 20s (!), after: 0.5s

Of course having a BBU is always recommended, but what are the main benefits of installing a BBU to a system, which got redundant power supplies and is attached to UPSs? Does this depend on the type of the system (database, file, terminal)?

What is a realistic fail scenario which could be prevented by a BBU?

Thanks in advance!

Every hour a server replicates SQL server data with some external web server. During this time, which takes about 2-5minutes, the database seriously slows down. Colleagues, which work with the front end applications of that on another terminal server, even regularly start complaining.

The databases are also synchroniously mirrored (via SQLServer mirroring, no replication) to a third server. Note that 99% of the data is replicated outgoing, so the server should rarely need to update its data.

As the (merge and transactional) replication tasks are not time-critical, I would like to reduce their priority or somehow slow them down, so they don't affect the database performance that much.

How would you implement that?

My question closely relates to my last question here on serverfault.

I was copying about 5GB from a 10 year old desktop computer to the server. The copy was done in Windows Explorer. In this situation I would assume the server to be bored by the dataflow.

But as usual with this server, it really slowed down. At least I could work with the remote session, even there was some serious latency. The copy took its time (20min?). In this time I went to a colleague and he tried to log in in the same server via remote desktop (for some other reason). It took about a minute to get to the login screen, a minute to open the control panel, a minute to open the performance monitor, ... Icons were loading maybe one per second. We saw the following (from memory):

• CPU: 2%
• Avg. Queue Length: 50
• Pages/sec: 115 (?)

There was no other considerable activity on the server. The server seldom serves some ASP.NET pages, which became also very slow in this time.

The relevant configuration is as follows:

• Windows 2003

• SEAGATE ST3500631NS (7200 rpm, 500 GB)

• LSI MegaRAID based RAID 5

• 4 disks, 1 hot spare

• Write Through

• No read-ahead
• Direct Cache Mode
• Harddisk-Cache-Mode: off

Is this normal behaviour for such a configuration? What measurements could give further clues?

Is it reasonable to reduce the priority of such copy I/O and favour other processes like the remote desktop? How would you do that?

Many thanks!

Today I was trying to execute a long-running (but highly optimized) query on a server in our company. I've run the same query on the same data on my home computer (5400rpm harddisk) and it took about 40min. The server is about 10 times slower, maybe still running! The server does about 30 inserts/second, my home computer 300 inserts/second.

As the CPU load is very low (3%), memory is plenty and network is not used as the query is executed locally I suspect the disk I/O to be the bottleneck. At my home computer instead (which got better CPU power) two cores were nearly fully loaded.

The relevant disk configuration is as follows:

• Windows 2003

• SEAGATE ST3500631NS (7200 rpm, 500 GB)

• LSI MegaRAID based RAID 5

• 4 disks, 1 hot spare

• Write Through

• No read-ahead
• Direct Cache Mode
• Harddisk-Cache-Mode: off

Surely, RAID5 may not be the fastest, but the disks compensate by being faster as at home.

Some further interesting data:

• Avg. Queue Length: 30
• Avg. Queue Length (Read) : 2

• Avg. Queue Length (Write): 26

• Bytes/s read: 1.3MB/s

• Bytes/s write: 1.2MB/s

• Sec/Read: 0.007

• Sec/Write: 0.500

• Writes per Second: 36

How can I resolve this write bottleneck? Is it because of the many small writes? Is there some malfunctioning driver or a possibility to cumulate some transactions for bigger writes?

The seconds per write are very large!

Thanks!