Create SQL service accounts fast

The past few months I had a few reinstalls of my local development environment due to hardware failure and a new work laptop. And I have installed a local SQL Server instance at least 4-5 times. Luckily, I created a silent install for SQL Server 2016 earlier this year, but I didn’t create a script for pre-install tasks, like creating SQL Server service accounts. Today, I took the time to create the script for creating these quickly.

In my local development environment, I normally install database engine, SSIS and agent. It is not completely necessary with these accounts locally, but it is a good practice. Change the password “XXX” to your own, and run the script from command-line.

NET USER svc-sql-db "XXX" /ADD /passwordchg:no /fullname:"SQL Engine service account"
NET LOCALGROUP "Administrators" "svc-sql-db" /add

NET USER svc-sql-ag "XXX" /ADD /passwordchg:no /fullname:"SQL Agent service account"
NET LOCALGROUP "Administrators" "svc-sql-ag" /add

NET USER svc-sql-is "XXX" /ADD /passwordchg:no /fullname:"SQL Integration service account"
NET LOCALGROUP "Administrators" "svc-sql-is" /add

The only thing I need to improve is to fix the “Password never expires” check box. NET USER have a “/expires:never» switch, but doesn’t seem to work. This could be written in Powershell, but found these command very easy.

Advertisements

Installing SQL Server 2008 R2

Since I have been writing blog on how to install previous version of SQL Server –  SQL-2000, SQL-2005, and partly SQL Server 2012, I decided to complete the series with installing SQL Server 2008 (R2). So, why do I write these posts? The first two posts I wrote just for the sake of nostalgia and fun. I thought it was nice to have a reference to older version and screenshot on how the SQL Server was done in the past. It will probably be nice to look back on in few years time.

Installing SQL Server does not differ much between versions. Off course, there are new features but there are always a set off pre-installation tasks you have to go through and consider before the installation can begin. In addition, there are a set of post-installation tasks. Brent Ozar has some good posts on pre-install tasks and post-install tasks for SQL Server. Jonathan Kehayias also has a very good and compact post SQL Server installation checklist.

When you have completed you reinstallation tasks, such as preparing disks, creating service accounts and so on, you will fire up the setup.exe from the installation media. In my case, I did the installation on a local VM instance, not on the network. As you might see from the screenshot below, I have created disks for OS, Data, Log, Tempdb and Backup, just our company standard.

SQL2008_install1

The startup of setup.exe might take some time, depending on the installation box, but when appears, click on the installation meny and choose “New Installation or add feature…” entry.

SQL2008_install2

The next step of the installation process will check if your box is ready to be installed.

SQL2008_install4

The next screen enables you to select the SQL Server edition you shall install. You can select an “Evalutation” licence, and you have 180 days to evaluate the installation. The product key is the second option. This might be default, depending on where you have downloaded the installation media.

SQL2008_install5

You have to accept the licence terms for SQL Server on the next screen. You are also enble to help Microsoft to collect information about what features that you use on the server. This will help them to prioritize most used features in future version, and decide what functionallity that should be depricated.

SQL2008_install6

The next step in the installation process is to copy support files to the local drive.

SQL2008_install7

Together with the support files, some setup rules are checked to see if the computer and OS is ready.

SQL2008_install8

The next step is to choose type of installation. The three choices is a full installation (the bottomn one) with all default values. If you chose this option, you are ending up with lots of post-installation tasks that could be easily configured during the installation. You also have the option to install PowerPivot for Sharepoint if you are managing a Sharepoint BI solution. The first option is the most feasable where you can choose whatever feature you want.

SQL2008_install9

The next step in the installation process is to select the necessary feature to serve clients with the functionally needed for their applications. I have selected a set of features that is common for a developer database. The database engine is almost required, but for my use, I don’t need Analysing and Reporting Services. In addition, I don’t need the Sync framework.

SQL2008_install10

As you might see above, the “Shared feature directories” is located at the C: drive. There are lot of options on wheather these files should be installed on the C: drive or another SQL-Bin drive. I like to set these to the C: drive and have never had any issues with this setup.

The screenshot below show the validation installation rules that is passed. If no errors occurs, the setup installation process will continue.

SQL2008_install11

The next step is the screen where you chose the instance where the database engine shall be installed. If you don’t have any good argument to choose named instance, you should choose the default instanse. A production server should be installed on the default instance. If you have other enviorment, you could an instance DB_TEST and/or DB_DEV. Security is another argument to should two or more instances.

SQL2008_install12

The next informational screen should the distribution for the different drives where the system files and shared fetaures.

SQL2008_install13

Service accounts are important for SQL Server, and most of the SQL Services should be assigned to a service account. This apply for at least db engine, Agent, Integration, Analysing and Reporting Services. Service accounts are normal domain users accounts. The service account for have some policy setting such as “Perform maintenance task” that should be assigned, but all other service accounts are pretty much only normal domain users.

I normally use this namning convension for a server named SQL-01, if customers don’t have other preferences:

  • Database engine = Svc-SqlDB-P-SQL01
  • Agent = Svc-SqlAG-SQL01
  • Integration Service = Svc-SqlIS-P-SQL01
  • Analysing Service = Svc-SqlAS-P-SQL01
  • Reporting Service = Svc-SqlRS-P-SQL01

If there are mulitple enviroment, I normally prefix the server name with P (Production), T (Test), Q (QA), D (Development).

SQL2008_install15

The next step of the installation process enable you decide how user shall authenticate against you server. SQL Server allow both Windows and SQL Server Authentication. When you choose both, it is called “Mixed Mode”, and you have to create a SA account (System Administrator) with a known passord to the DBA for the particular server. You have to write down the password and store it a safe place. I recommend KeePass where you can organize all you usernames and password for your servers in a safe way. I also recommend to have a long and complex master password.

SQL2008_install16

It is normal to add either one person or an AD group as SQL administrators, in addition to the SA account. If you create an AD group SQLAdmins, you can add this group during the installation, and just add new persons in this AD group when you get a new SQL DBA for your servers.

The next screen is maybe the one where beginner do most errors. Been there, done that. The first directory (1) is where all the SQL Server binaries shall be installed. This included the system databases, such as master, model and msdb. The tempdb system database should be installed a dedicated drive (LUN), shown in directory (4). The data files for all user databases should also be located on a dedicated disk, shown in directory (2). The same goes for the Log files for the user databases (3).

SQL2008_install17

The location of the backup files is widely discussed (5). Some mean that the backup should be located on a dedicated disk locally on the server for performance, and be pulled by the enterprise backup software to be stored in a secure location. Other prefers to store the backup files directly on a network share within the same SAN system. Others again stores the backups on a different SAN system is case the SAN system collapse. But backup and restore strategies are a huge topic, and I normally don’t consider this for the customers if they don’t mention when I deliver my requirements for the SQL Server.

The feature FILESTREAM must be enbled if you want to use it from T-SQL. This feature is enhanced with the FileTable in SQL Server 2012, and will not be covered in this blog post.

SQL2008_install18

The final screen before the actual installation start is show below. It shows all the features that you have selected and configured. At this point, SQL Server setup program has created a configuration file with all these settings. The path is shown in the red recangle. This configuration file can be used to create an automatic installation script for unattended install for that can be used for all you SQL Server 2008 R2 servers.

SQL2008_install20

After you have pressed “Install” button in the previous screen, the installation progress is shown as the screenshot below.

SQL2008_install21

When the installation has compled successfully the following screen is shown. The full installation process can be found in the file path shown in the red rectangle.

SQL2008_install22

Now, you have a fully installed server, and the post-installation tasks has to be completed before the server is usable for others. See the post from Ozar post-install tasks and Kehayias checklist for more information.

I normally complete the following tasks:

  • Set “Max Memory” to approx 70-80% rule. Here is the numbers I use for server with total memory:
    •   4GB total =>  2048 MB
    •   8GB total =>  4096 MB
    •  16GB total => 11264 MB
    •  32GB total => 25600 MB
    •  64GB total => 54272 MB
  • Make sure port 1433 is open in the firewall and TCP protocol is enabled.
  • Make sure the Browser and Agent services are enabled, running and configured to Automatic
  • Verify the firewall setting for the sqlservr.exe
  • Verify “AutoClose = false”
  • Verify “AutoShrink = false”
  • If customer doesn’t have any backup strategy, I normally install and configure the maintenance script til Ola Hallengren. This is widely recongnized in the SQL community. I also create addidional Agent Schedules for “midnight”, “evening” and “morning” to make is easier to add jobs for these times of day.
    • Configure daily backup of system databases
    • Configure daily full backup of all user databases
    • Configure weekly (sunday) index/statistics mainteance and standard cleanup tasks

Installing SQL Server 2000

A bit of nostalgia today. I decided to install a VMware instance with Windows Server 2003 x86 and SQL Server 2000 x86. I will use it to prepare script for my co-workers in the new project where we shall migrate work in a project where 6 system are running on this configuration. It’s over 4 year since I worked with SQL Server 2000 on regular basis, and I need a servere where I can try out thing on my own. This VM instance can also be used my collegues for testing legacy systems.

Microsoft support and liftcycle

Microsoft does not support SQL Server 2000 anymore. The “Extended Support” expired April 9th 2013. Windows Server 2003 is also in “Extended Support” and will expire July 14th 2015. Here is a summary of the lifecycle for Windows Server 2003 and SQL Server 2000.

Lifecycle SQL Server 2000:

  • Mainstream Support: 8-APR-2008
  • Extended Support:   9-APR-2013

Lifecycle Windows Server 2003:

  • Mainstream Support: 13-JUL-2010
  • Extended Support:   14-JUL-2015

Migration strategies

It is not decided what SQL Server version we shall use in the target enviroment, but there are several options. The first option is to migrate the server “as-is”. This mean create a new server instance in the target enviroment with Windows Server 2003 and SQL Server 2000. This option involve less risk for the project, and the customer can use current licences for OS and DB. But the customer will have to upgrade the system in the near future if they want support from Microsoft if something happens.

A second option, is to install a new server with a newer version of Windows Server and and SQL Server and run the database in “Compability Level”. The different versions of SQL server supports 3 version back. This can be archieved by using the “ALTER DATABASE Compatibility Level” command. This options work pretty well, and I have used it for several systems where the customer does not want to take the risk of fully upgrading the application.

The third option is to perform a full upgrade of the server architecture and fully convert the application to a newer SQL Server version and take advantage of new fatures. This often require more time to complete, but the end-users and database administrators will most likely get a better application experience in their daily work.

Installing

SQL Server is a very I/O intensive application. As a result we should seperate the data and log files for the SQL server. I haven’t described any pre-installation tasks as I did for the “SQL Server 2012 installation” post which included tasks such as definition of service account, measuring disk performance and serveral other tasks that is important when you install SQL Server 2012.

The first “real” screen let you choose on which server the installation should be performed. I have never installed SQL Server 2000 with other than “Local Server”, and have no intention to try out the other two options 🙂

screen1

The second screen let you create a new database instance, upgrade an extisting or advanced installation with clustering. I have chosen a standard new default installation for this case.

screen2

The next screen is just a where you put your name or department together with the company name that owns the licence to the SQL Server.

screen3

The next screen let you choose how detailed the installation will be.. You can choose to only install client tools that let you connect to a SQL Server 2000 server instance through a common GUI interface such as Enterprise Manager and Query Analyser. The second choice is the full  installation. The third choice is just drivers and APIs/SDKs for connection to a SQL Server through other development tools such as Visual Studio.

screen4

The following screen let you decide whether to install on the default instance or to create a new named instance. The most common option is to choose the default instance. But an example, is to create two instances on the same server – one for test and one for development.

screen5

The next few screens shows the different “Size” options – typical, Minimum and Custom, all with different disk space requirement.

screen6

screen7

screen8

If you select the custom installation, the next screen let you choose which components you want to install, including extra components as documentation, examples and development tools.

screen9

The next screen let you define service account for the different components on SQL Server 2000. It is normal to create one account for each service, but it is possible to use the local system account for installation purpose, and define the service accounts when configuring the server properties in the post-install tasks. This is the case for this post, but the service accounts are defined in similar manner as for SQL Server 2012.

screen10

The next screen let you define how clients should be able to connect to the SQL Server, which supports 2 modes – Windows Authentication and Mixed Mode. This work equally as the other versions (for example SQL Server 2012). When you choose Mixed Mode, you will define a password for the sa system administrator accounts.

screen11

The next screen defines the server collation that determine the character set that is commonly used on the server and how string should be sorted and how different datatypes should be displayed to the clients.

screen12

The next screen defined the licence mode for the SQL Server. It is possible to choose “Per Seat” (client) or “Processor Licence” (Pr. CPU/Core).

screen15

When the installation is completed, you will have to install some service packs since the SQL Server is pretty old. In my case, the SP2 was included on the installation media, and I have to install the SP3 and SP4 manually. Here is a link for latest service packs for SQL Server 2000.

screen16_sp3

screen16_sp4

Finally done…just some configuration, setting up backup and maintenance tasks, and I’m ready for my last crusade with SQL Server 2000. This will probably last for a while, but hopefully, the customer will see the need for an instant upgrade project when the current migration project is completed.