MSSQL server administration 2

[theScien]

I'm new in the DBA position recently aquired, and I'm having a few issues with MSSQL configuration and administration.

1. I have created a two maintenance plans, one for system databases and another one for user databases, this plans include things such as, Integrity Checks, Optimizations and DB Backups, however the Integrity Checks fail with the error that the DB's must be in single user mode, how can I go about to make this and what are the implications of doing so?

2. There's been a few reports of MSSQL server compromises through UDP port 1433, so I closed it at firewall level, only TCP 1433 is going through, do I really need UDP 1433?

Thank you very much.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[mrdenny]

1. Setting a database into single user mode will stop any one from connecting to it except for the maintenaince script while it's running. In your maintenaince plan, uncheck the box that says to repair any errors. This will stop the db from trying to go into single user mode. Then when it runs, if there is a problem, it will be noted in the log. You can then take the needed action to correct the problem.

2. MSSQL uses UDP port 1434 not 1433. It uses TCP port 1433. This bug is fixed with SP3, which would be applied asap as it fixes many other potential issues. If nothing needs to access the server from outside your network, I'd close TCP port 1433 as well. Ports should be closed unless needed, not open unless a problem comes up.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[theScien]

Thanks mrdenny, the Integrity checks run fine now, however without the recommended auto error repair, what else should I know abou this and the single user mode?

MSSQL has SP3a installed since it was available, and the server has all ports and services disabled unless necessary, I have UDP 1434 closed, should I expect problems? The server is used for Internet and users need to connect to it to administer their web site databases.

Thank you.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[mrdenny]

If an error does come up, you will need to place the database in single user mode to repair the database. You can do this with the alter database command.

alter database [database_name]
set single_user
go
dbcc checkdb (database_name, repair_rebuild)
go
alter database [datebase_name]
set multi_user
go

The repair level will depend on the error being reported. The log from the maintenance plan should tell you the repair level that you need to use. The repair level is the second paramater of the dbcc checkdb command.

We also have several customers that maintain databases on our network. We have provided web based access only to the database for security reasons. This allows us to only allow the web servers access to the SQL Server, and not the users. If the users need a large amount of data loaded into the database, they upload the file to there web site, and submit a ticket to me to have the data loaded into the table.

Microsoft has just release there own web based SQL Admin. You can download it from

http://www.microsoft.com/downloads/...8-c57a-419e-acbc-2a332cb7f959&displaylang=en.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[theScien]

Thanks once again mrdenny, I haven't received any error, however your code is useful in case a repair needs to be performed.

I will consider the web admin route only, as for the UDP 1434 being closed, I haven't received any connection problem from any user, just wondering now what MSSQL does with UDP 1434. Do you know?

Thanks.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[mrdenny]

If you only have one instance of MSSQL installed then the UDP 1434 really isn't needed. It comes into play when you have more than one instance installed.

With a single instance installed listening over TCP the server listens on port 1433. When you install additional instances, they listen on other ports. These ports are dynamically assigned based on what's available at the time of install. Usually 1434 and up. When the client tries to connect, it will connect the the UDP port 1434, and tell the listener that it's looking for instance Something. The listener will then respond with a TCP port number (as well as some other connection info that's not relivant to this). The client now knows which TCP port to connect to for the instance that it's looking for.

That UDP port is kind of like 411 for the SQL Server. When your only running a single instance, you use port 1433 as default, so you don't really need to worry about the UDP port.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[theScien]

Thanks, well explained, my settings are fine and that's the important thing.

------------------------------

http://www.mylittlehost.com

------------------------------