Posts

Showing posts from May, 2012

SQL Server Memory Settings

It's hard to get a fix on what is the recommended memory settings for SQL Server. It seems to be one of those questions that if you ten DBAs what settings they should use you will get ten different answers. My take on things is pretty much in line with the suggestions from Brent Ozar and Glenn Berry - I've been setting up SQL Servers for years using similar settings to their suggestions without any real memory type issues. The only caveat I have is that as good as their suggestions are, each case is unique and the memory settings need to be monitored and adjusted accordingly. If you've got no idea what the memory settings should be then this is a good place to start followed up by some highly recommended reading at the links below.
Before going any further it's good to keep in mind what the SQL Server memory settings actually do. They control the target memory usage of the SQL Server Memory Manager. That's important because these aren't hard and fast values - SQ…

Database Ownership in SQL Server

Database ownership is one of the most important and overlooked configuration items when creating or restoring databases. The account that owns the database is mapped to the dbo user in that database giving the user full permissions to the database - this includes permission to drop the database! 
The reason it gets overlooked is that once a database is setup we often don't review the database settings. When we provide access to the database we do it via the logins or security pages. Also the creator of the database is set to the owner by default. This is also the case for restoring databases. Other problems can occur if the original creator of the database leaves the organisation. In this case they may still have full permissions to the database long after they've left the organisation.
It's best to create a standard for your databases including setting a standard database owner. Typically I set the database owner to the 'sa' account. To change the owner of a data…

SQL Server Memory Configuration Guidelines

Note: These guidelines are general in nature and are designed for a single instance of SQL Server.

The minimum SQL Server memory should be changed from the default setting of zero.The reason for this is that when the operating system comes under memory pressure it will tell the applications running on the server to return memory to the operating system. SQL Server's response is to release memory based on the minimum memory setting. The recommendation is to set the minimum memory to 50% of the server's physical memory so that SQL Server can co-operate with the operating system but not sacrifice all of it's memory.

The maximum SQL Server memory should be changed from the incredibly high number provided to a smaller number. A good value for the maximum memory setting is to either leave 2GB or 10% of the server memory free for the operating system and other applications, whichever is greater.

It is also recommended to not use the "Lock Pages in Memory" feature as th…

Configure SQL Server Reporting Services 2008 R2 To Use SSL

The very brief outline of how to setup SSL access in SQL Server Reporting Services:
First install the SSL Certificate in IIS or Server Certificates: Open MMCGo to File --> Add SnapinSelect CertificatesImport CertificateUse Reporting Services Configuration Manager to setup the the SSL / 443 in SSRS.
Next edit RSReportServer.config and add the new report server URL 
eg.
<ReportServerUrl>https://server_name/ReportServer_PRD</ReportServerUrl>
Restart the reporting service for the settings to take affect.
Note: The report server has to be accessed using the path configured above. This must be a valid resolvable path and must conform to the SSL certificate issuer address. To test the procedure has worked just edit the hosts file and try connecting with the URL above.

Ubuntu - Setup Group Share on Samba

The purpose of this procedure is to setup access to a FTP site using a SAMBA share. All users will access the same share. This procedure has been tested against Ubuntu 10.04 LTSThere's a few steps that have been done before running this procedure. We've configured the FTP server in this procedure which has created the following: A folder called /srvA folder called /srv/ftpA folder called /srv/ftp/sharedA linux group called ftpsharedThe group ftpshared has been given root:ftpshared access to /srv/ftp/shared
First we start by installing the samba packages:
sudo apt-get install samba sudo service smbd stop
Before we start modifying the Samba configuration we will backup the existing samba configs:
sudo mv /etc/samba/smb.conf /etc/samba.smb.conf.bak
Setup a new blank smb.conf file:
sudo nano /etc/samba/smb.conf
Now paste the following into the smb.conf file: [global]
    ; General server settings
    netbios name = server_name
    server string =
    workgroup = WORKGROUP
    announce versio…

Ubuntu - Setup a SFTP Server

This procedure will setup a SFTP server, thats FTP over SSH on Ubuntu Server. In this instance the FTP server will be for a group of people accessing the same FTP share.
This procedure has been tested against Ubuntu 10.04 LTSFirst we need to install the SSH-Server packages on the Ubuntu server assuming they haven't been installed already. On the console type:

sudo apt-get install openssh-server
Next we will create a group which will have shared access to the FTP Server mount point:
sudo groupadd ftpshared
Next we create the FTP folder on the filesystem. As this folder will contain data that may potentially grow to fill the disk it is recommended to set this up on a separate partition. Another important point to note is that SFTP relies on both the root user and root group owner the top level FTP mount. If this is not the case you will get errors when connecting to the FTP server, in my case authentication and connection errors.
sudo mkdir /srv sudo mkdir /srv/ftp sudo mkdir /srv/ftp/share…

Ports Used By Active Directory

Windows Active Directory Domain Controllers use the following ports:
DescriptionPort NumbersProtocolLDAP389TCP + UDPLDAP SSL636TCPLDAP GC3268TCPLDAP GC SSL3269TCPKerberos88TCP + UDPDNS53TCP + UDPSMB,CIFS,SMB2, DFSN, LSARPC, NbtSS, NetLogonR, SamR, SrvSvc445TCP + UDPTCP Dynamic - User and Computer Authentication, Group Policy49152-65535TCPWindows Time123UDPRPC5722TCPUDP Dynamic - Group Policy (DCOM, RPC, EPM)49152-65535UDPGroup Policy, Netlogon, NetBIOS Datagram Service138UDPUser and Computer Authentication - NetLogon, NetBIOS Name Resolution137UDPUser and Computer Authentication - NetBIOS Session Service, NetLogon139TCPUser and Computer Authentication, Kerberos change/set password464TCP + UDP

Ubuntu - Add Another Disk To The Server

The following procedure assumes that the additional disk has been physically added to the server and needs to be added to the operating system.
This procedure has been tested against Ubuntu 10.04 LTS
First we confirm details of second disk by running the lshw command. If all goes well here we should see the original disk and the new disk appear here. Take note of the location of the disk, in this case it will be /dev/sdb
sudo lshw -C disk
Now open fdisk partitioning tool to create partitions on this disk.
sudo fdisk /dev/sdb
The fdisk tool will display a menu. Run through the following steps to creae a new partition that takes the entire disk: Type "n" and "enter" to create a new partitionType "p" to create a primary partitionType "1" to create 1 partitionPress enter to accept the default start position of the partitionPress enter to accept the default end position of the partitionType "w" to write the partition Now that we have create a parti…