Database Basics

Run SQL Server Management Studio. Select Database Engine as Server Type, . for Server Name  and Windows Authentication for Authentication type. What you see after you connect is server.  Do not create tables, views, objects (triggers, functions) etc in system databases. SQL Sever does not guarantee that tables, objects and views will be updated as you upgrade the version later on.

Creating Database

Right click on Databases click New Database. Name it Test. You will see two files.. Hit Ok.
1. Test
2. Test_log

The idea here is that you want to populate your Test database with different things like tables and views. But before we do that let’s look into two things. Let’s find out the location of Test & Test_Log and differentiate between an .mdf and .ldf file. Just go to Program Files and Click on Microsoft Sql Server then MSSQLL10_50.MSSQLSERVER. The snapshot shows the location of the database we just created.

What is the difference between .mdf and.ldf file?
MDF file, known as the primary database file, contains the schema and data. A LDF file contains the logs. A database may also use secondary database file, which normally uses a .ndf extension.

Backing Up and Restoring Database

Right click your database Test. Click on Tasks and then Backup tab.

If you want to restore, the way to do it is simply by right clicking the database you want to restore. Click  Task and Restore. It will Rollback to where you last backed up. You can even rollback to specific date and time. More later.

Attaching Database

Let’s attach our database three  ‘Adventureworks’ file to server. To do so, right click Databases and select ‘attach’ file. Now locate the mdf files  and you are all set.

SQL SERVER Background Info (Part-3)

I hope by now you have SQL Server installed. In this post I will quickly talk about database design and give you a quick overview of different things.

Database Design (Quick Snapshot):

Your database initially is empty or there may be a need to add data at some point. Therefore, you need to be able to design your database efficiently. Database Design is simply a way of getting tables you need right. So department table will have things like department id , department name, department location etc. This is a job of data architect. The design process has three phases in general.

1. Gathering requirements from BA. Verifying relationship and creating conceptual diagrams.
2. Coming up with logical diagram. Architect comes up with table names, dependencies and data types etc.
3. Physical model.

Usually the above process can be carried out in Erwin or SQL Power Architect. It’s relatively straight forward. In the end of the process you can generate a schema using forward engineering. Hit the ‘Generate‘ button and Connect to SQL Server (using windows authentication or whatever system you have) and your tables will be created from Erwin. You can also generate scripts for tables you have and copy paste them in SQL server. Remember database have different objects like tables, views , stored procedures, functions, trigeers etc but tables hold the real data.

Noobs 3 Best Practices For Database Design:

1. Document your ER diagram.
2. Consistency in naming.
3. Spend more time designing.

Overview:

When you install your server, you will not get any databases. You will only get system databases which are master, model, tempdb and msdb. Please do no create tables on system databases. Why? If you do, upgrading later will be a big problem. We will soon talk about attaching the database we downloaded in Part-1 in SQL Server Management Studio.

One last thing. Majority of business users are never concerned about all this stuff. All they want is access to reports. Where are the reports? In SQL SERVER REPORTING SERVICES you will have an URL where reports are hosted. You have report server name like  https://mystartup/reports/folders.aspx. In other companies they have sharepoint portal. Report is usually tested and deployed. If users want to add column then the whole life cycle is repeated. There is no Ad-hoc in this scenario. We need to be able to give users Ad-hoc capabilities. To do so, we need cubes and give users access to them. I really didn’t want to go through all this but again this will sets up a stage for my future posts.

In excel, either you can connect to SQL SERVER directly and import data in or you can use Analysis Services to retrieve cubes. In order to create a cube you need a Data mart. Data mart is simply an access layer of your Data Warehouse.  We can create a new data mart for user requirements and write an ETL using SSIS. Phew…