Wonderful Xmas!

Merry Xmas to everyone, have a very nice tech XP and personal development for the new 2012!

Short Tips... SQL Server Configuration Manager

Here I am (again) to let you know something that most of you probably know much better than me.

This little suggestion helps a lot for avoiding so many mistakes and hard times...

If you need to do "any" configuration task remember to use SQL Server Congifuration Manager, it does integrate all you need to start, pause, stop, restart, a SQL Server Service, and also you can do your regular administration task thru it. For example, you will need to change password accounts regularly, but you don´t want to stop, or even restart SQL Server each time you do your security maintenance tasks. If you use SQL Server Configuration Manager you can change the password of SQL Server Service Accounts with no longer need to restart the SQL Server Instance. All theses changes are available inmediately...

Warning: You should never, ever use Windows Service applet again to do these jobs. It will give you very hard times. Every time you change a password there, you will be forced to restart your SQL Server Instance again, so compromising your production environment availability... take it easy and remember "SQL Server Configuration Manager" always!

Good luck folks
Jaime Gibertoni

SQL Server Database Mail feature

SQL Server Database Mail

This feature enables a computer running SQL Server to send outbound mail messages. It can be used for sending messages to DBAs  when an error or performance condition happens or if there is a change in some database objects. This is very useful and it was introduce to SQL Server 2005 in order to avoid the use of SQL Server Mail feature (already linked to MAPI), and to give SQL Server more independence from subjacent local machine services. This makes it easier and more reliable when sending messages from SQL Server instances.

Database Mail uses SMTP (Simple Mail Transfer Protocol) relay service on Windows Local machine for transmitting all mailed messages. Every time a mail message is sent by SQL Server, it is logged into a table in Msdb (database). On a regular basis (periodically) SQL Server Agent Executes and runs a mail send process, and all messages inside the send queue are forwarded using the appropriate mail profile.

Database Mail gives you a failover feature when sending e-mail messages, every single profile can use many e-mail accounts so if one of these e-mail server is down, there is always a way to send and guarantee that the message is sent and delivered to the recipient.

There are also public and private profiles. Public ones let all users to use them to send any message they want to be sent. On the other hand, Private profiles restrict users for sending messages, only SQL Server will use these profiles to send queued messages.

Also users can have access granted to their SQL Server Database Mail Private Profile explicitly. It is also mandatory, when sending messages to specify the public or private mail profile to be used (otherwise, SQL Server will use the profile you setup as default to send all these mail messages).

If your messages are located at Msdb but never delivered, please check out SQL Server Agent (it must be running to send all queued messages)

So, now we are ready to setup Database Mail in our SQL Server Instance, to do so:

1. Open SQL Server Management Studio, right click on the server instance you want to activate Database Mail, and select New Query, this will open a blank query window,
2. Type the following (or copy/paste) Transact-SQL code for enabling Database Mail Feature on your instance:

EXEC sp_configure 'Database Mail XPs',1

GO

RECONFIGURE WITH OVERRIDE

GO

3. At Object Explorer, open the Management node, right click Database Mail, and select Configure Database Mail,
4. Click Next on the welcome screen, then
5. Click Set Up Database Mail, do the following steps, when completed click Next,
6. Give a name to your profile, click Add button to select mail account settings,
7. Then fill in all the fields (Account Name, E-mail Address, Display Name, Reply E-mail, and Server Name). If you are using and internal mail server you have to specify Server Name (if no your internet mail server account will be used as default.
8. Chose the right SMTP Authentication mode for your organization and, specify username and password (basic authentication).
9. Click Ok, and Next…
10. Select the check box in the Public column next to the recently created profile, also set this profile to YES in the Default Profile column, click Next
11. Review all the settings (this is Configure System Parameters page), then click Next
12. Click Ok, and again click Next. Click Finish.
13. The final page should show that all four configuration steps completed successfully. Click Close.
14. Right click on Database Mail, and then select Send Test E-Mail
15. Select the Database Mail profile you just created, enter an e-mail address in the To: field, and click Send Text E-mail,
16. Go and check if your e-mail client received the e-mail you just sent.

Remember to keep up running SQL Server Agent, if you do not do so, all your sent messages will be held in the Msdb database, and will never be sent.

Best regards to you my friends,

Jaime Gibertoni, tweets to @jaimegibertoni

SQL Server Instances installation

SQL Server Instances

SQL Server Instances define the container of all tasks you can perform within SQL Server Platform. For each instance, it is created its own set of databases, security credentials, configuration settings, services running under Windows, and so many others SQL Server Objects.

You can install up to 50 different instances of SQL Server under the same machine (including virtual machines). It could be 50 named instances, or 1 default and 49 named instances. You refer to each instance as <machine>\<instance_name>, remember that you can hide all instances setting off the SQL Server Instance Discoverable feature (whether the instance response to an enumeration request).

Installing more than one instance let you:

• Test QA testing or development
• You can support multiple service packs or patch levels
• Different DBA has access to as many as databases they require (group access permissions)
• Need of supporting multiple sets of SQL Server configuration options

There is only one limitation, for installation purposes, only SQL Server Enterprise Edition is licensed to install 50 concurrent instances. Remember, each instance, each database and other objects such as tables, columns, can be set up to use different collations.

SQL Server Configuration Manager

This applet is responsible for giving you the chance for managing SQL Server Installed Services and Protocols. Some important tasks that you will be able to perform are

• Start, stopping, pausing and restarting services
• Change Service Accounts and their password (do it only inside this applet, never use Windows Service applet to change password, or starting, stopping and pausing services)
• Managing start-up modes of a service
• Configuring different service start-up parameters

Just remember one thing, SQL Configuration Manager is the only one applet that let you do all this changes without the need of SQL Server restarting. You can also change all account passwords and the changes will be available immediately.

The Configuration Manager is the only one who has the code necessary for regeneration of master keys critical to the operation of all SQL Server services.

I will give you some recipes to install and configure a SQL Server Instance:

Creating Service Accounts:

1. Click Start, right click My Computer (Computer), select Manage, a Windows Management windows shows up
2. Expand Users and Local Groups (Windows 7)
3. Right click at Users (folder) and select New User.
4. In the username field type SQLSERVER_ENGINE, for a SQL Server service account for the database engine, supply the account with a very strong password, clear both User Must Change Password At Next Logon and Password Never Expires check boxes.
5. Repeat all these steps until you complete your service accounts (desirable more than two different accounts, one for database engine and the other for SQL Server Agent)

Installing a SQL Server Instance:

1. Start SQL Server installation routine
2. If your prerequisites are not installed, the setup will install them for you
3. At the SQL server installation windows select Installation link (at the left panel in front of you)
4. Click the New SQL Server Stand-alone Installation link,
5. Installation process begins and executes a configuration check out. If this check completes successfully it will show a status screen with some items checked "passed".
6. Click Ok, next: Select the server edition you want to install, and then click Next,
7. Select the features that your SQL Server instance will be running, then click Next,
8. Select Default Instance, enter the name of the service accounts you have created before (and their passwords)

Now it is important to mention that you have to go to Collation notch and select the collation sequence you are going to use by default at the instance level. Please take a little time to chose the right collation sequence according to the language(s) you are working with and then click Next.

9. Database Engine configuration page will show up, select mixed mode (SQL Server and Windows authentication) and set a password, click Add Current User to add the Windows account that you are running the installation under as an administrator within SQL Server. Click Add to add any other Windows accounts that you want as administrators within SQL Server
10. Click on the data directories tab to review the settings,

It is a very good practice to keep in mind file locations. For example, it will be faster and safe to keep most of these files in different hard disks. For security, performance and backup/restore policy reasons.

12. Click on the Filestream tab, select Filestream for Transact-SQL, enable Filestream for File I/O Streaming access. Put the Windows share name set to the required name or leave it to the default MSSQLSERVER, click Next. (If for some reason you forgot to enable Filestreams, you can do so from SQL Server Configuration Manager, anytime later)

13. Answer all other remaining options and review the information at the Ready to Install page. When satisfied click Install.

SQL Server will be reporting you every single step and change it made. My opinion is that SQL Server Setup routine has become not only easy, but more intuitive and complete than previous SQL Server releases. If you are taking Exam 70-432 I invite you to check out a little bit more details on this process using the references listed in past issues of this post.

Once again, thank you very much for joining us in this new project for offering a SQL Server and C# common places for C# Developers.

Best regards

Jaime Gibertoni

twitter: @jaimegibertoni

Installing and configuring SQL Server Instances

Installing and configuring SQL Server Instances

In this review the following topics will be covered:

• Creation of service accounts
• Installation of an SQL Server 2008 instance
• Collation sequences
• Authentication modes
• Installation of sample databases (next issue)
• Configure a SQL Server Instance (next issue)

Service Accounts

Core SQL Server components run as services. Each component should be configured, for security reasons, with several service accounts, experts recommend using an account for every separate service. Each one should have it own set of administrator rights. You will need dedicated service accounts at least for the following components:

• Database Engine
• SQL Server Agent

Each service account provides for SQL Service both data and scheduling services and also defines a security boundary. Each SQL Service services will be granted with permissions to use all the resources needed by the instance (memory, processors, disk space, and networking). The service account should grant all permissions needed by SQL Server Database Engine (for example) for it to accomplished all required tasks.

Later we will discussed SQL Server Security with deeper review.

Remember when choosing service accounts this ones should be local accounts for a local instance and domain accounts if local machine is registered within a domain (the service account should be a registered domain account with all permissions needed granted).

Collation Sequences

Collation sequences control how SQL Server deals with your data. Natively it will work with a previously default collation sequence, it is a SQL Server instance level collation sequence (it could be an instance, database, table or even a column level collation sequence, each of these could be selected at design time). SQL Server default instance collation sequence will be selected at installation time.

The instance collation is mandatory and it will be overridden anytime in databases, tables, or columns when necessary.

By definition collation sequences treats data at character level in special way so storage, retrieval, sorting and comparison operations can be fulfilled without any trouble regarding the culture involved. So to speak if we use a collation sequence that is Case Insensitive and Accent Insensitive (CI & AI) will store, retrieve, sort and compare the way that we do in English language. If we chose AS (accent sensitive) and CS, we are using a French Collation to storage, retrieve, sort and compare data. If we are talking of "e" character, in French we can see e, é, E, É, è, È are treated differently.

Authentication modes

Authentication modes are set either to:

• Windows Only (integrated security)
• Windows and SQL Server (mixed mode)

When SQL Server is set with Windows-only authentication, use only Windows accounts to log in to the instance. If SQL Server is set in mixed mode, you can use either Windows accounts or SQL Server–created accounts to log in to the SQL Server instance anytime.

Best regards,

Jaime Gibertoni

Today we are about to learn a little bit of "Selecting SQL Server Editions" ...

Microsoft Windows SQL Server 2008 Editions come with so many flavors oriented to different kind of clients and needs. It starts with SQL Server Compact Edition with almost no footprint, ideal for mobile applications, and become more and more powerful that it could handle petabytes of data or millions of concurrent users. This is one reason for SQL Server to be called Database Platform instead of the traditional relational database management system (RDBMS).

Flavors? Which ones?

SQL Server 2008 Enterprise Edition, for the largest organizations

SQL Server 2008 Workgroup Edition, suited for small interdepartmental projects that need a very low set of features

SQL Server 2008 Standard Edition, for midsize to small organizations with no needs for Enterprise Edition features

SQL Server 2008 Express Edition, a very powerful free version suited for small web applications

SQL Server 2008 Developer Edition, for us but with no availability for being install on production environments

SQL Server 2008 Compact Edition, an embed database

SQL Server 2008 Evaluation Edition (180 days), not allowed to be used in production environments but fully functional for the Enterprise Evaluation (included for developers and DBA administrators)

As rule of thumb, DEE (Developer, Enterprise and Evaluation) are the same, but Evaluation and Developer are not allowed to be used in production environments (and Evaluation expires in 180 days), but their available set of features are all the same.

From now on I will resume where and how each one could fit better, and also which of SQL Server Services (Analysis Services, Service Broker, Integration Services, Reporting Services, and so on…) will be needed before installation, for fulfillment of your organization needs!

In example, Service Broker deals with amazing ways to communicate with queued messages, with asynchronous data processing capabilities. You can provide advanced business process orchestration handling data processing across a myriad of platforms sources, without the need for the user to wait  for process completion, or any other impacts.

Talking a little bit of SSIS (Integration Services) we can conclude that is a perfect step-up for old Data Transformation Services (Extract, Transform and Load or ETL). It uses workflows to evaluate if tasks fails or succeed to keep routing new tasks to be perform. If SSIS doesn´t have the tasks you need to work with, SQL integration with Visual Studio Tools for Applications let you create your own CLR (Common Language Runtime) processes to run and complete those  tasks not included in SQL Server Integration Services platform. Even though you can create your own executable or assembly to be registered and executed inside SSIS!

When we are worried about reporting tasks, SQL Server Reporting Services (SSRS ) comes to play hard with two main components, the report server and the report designer. The report server hosts and secures all the generated reports (on the fly or scheduled automatic generation). When users requested for reports,  the report server is responsible for connecting to the underlying data sources, gathering data, and rendering the report into the final presentation output. Rendering requires the report designer, which is responsible for all the activities involved in creating and debugging reports. Components are included that allow users to create both simple tabular or matrix reports and more complex reports with multiple levels of sub reports, nested reports, charts, linked reports, and links to external resources.

When IT is requested for solving too many questions work loads become time consuming. SQL Server Analysis Services (SSAS) was created to fill the gap between the data needs of business users and the ability of IT people to provide data solutions. SSAS is comprised of two main components:

Online Analytical Processing (OLAP) and Data Mining services. You need to master a little bit of SQL Server Business Intelligence (SSBI) to create cubes and other BI objects.

For further reviews on these and other topics, I do recommend Microsoft SQL Server 2008 Step by Step (Microsoft Press, 2008), this book is very well designed and an easy companion with SQL Server 2008 learning.

If you are going to take Exam 70-432 you should understand the editions, their features available, hardware support and requirements, and also the final purpose of the flavor itself (it depends on all the features discussed above).

It´s true that this subject is very exciting and than only words cannot accomplish the learning needs all we have. The next issues will be more interactive, with some exercises, examples and SQL code, when possible…

Thanks again for visiting this blog, please remember that your suggestions, opinions, points of view, and your valuable knowledge are all welcome to this place…

Best regards

Jaime Gibertoni

Preparing for SQL Server 2008 Installation for C# Developer as described on MCTS certification exam 70-432

This section contains all the review items completed in February 25th 2011, for you my colleagues!

Outline lectures Installing and Configuring SQL Server 2008 Exam 70-432 Objectives: Install SQL Server 2008 and related services Configure independent SQL Server Instances Configure correctly SQL Server services Configure Additional SQL Server components Implementation of Database Mail. Lessons covered in the chapter #1: Lesson 1: Determine Hardware and Software Requirements Lesson 2: Selecting SQL Server Editions Lesson 3: Installing and Configuring SQL Server instances Lesson 4: Configuring Database Mail

Before you begin (general suggestions): Hardware requirements fulfillment, SQL Server 2008 Installation Media (CD/DVD). To be sure How your SQL server instance is going to be used before installation (backwards means loosing too many time in reinstallation). Most common errors are: undersizing hardisk requirements, undersizing tempdb disk space. Taking into account concurrent users, final database size on disk drives, and configuring other services such as Service Broker, CLR, replication, clustering, data types, filestreams, and so on. The minimum requirements are specified for SQL installation purposes. Later on you will be able to get more information to make a good guess in software and hardware requirements: number and type of CPU, OS requirements, disks drives and so forth, for a given application. Hardware SQL Server Requisites 32 bits 64 bits  Processor  Processor Speed  Memory  Pentium III or higher  1.0 GHz or higher  512 MB Itanium, Opteron, Athelon, or Xeon/Pentium w/EM64T support 1.6 GHz or higher 512 MB Hard disk requirements should be reviewed at "Hardware and Software Requirements for installing SQL Server 2008"