Overview of SQL Server and its components

SQL Server is a powerful relational database management system developed by Microsoft. It is used by businesses and organizations of all sizes to store and manage their data. In this article, we will provide an overview of SQL Server and its components.

What is SQL Server?

SQL Server is a software platform for creating and managing databases. It is designed to be highly scalable, secure, and reliable. SQL Server provides a range of features for managing data, including support for transactions, referential integrity, and data replication.

SQL Server is available in several editions, each of which is tailored to the needs of different types of users. The editions range from the free Express edition to the enterprise-level Datacenter edition. The different editions vary in terms of the number of processors they support, the amount of memory they can utilize, and the features they offer.

SQL Server Components

SQL Server consists of several components, each of which serves a specific purpose in the data management process. Here are the key components of SQL Server:

1. SQL Server Database Engine: The SQL Server Database Engine is the core component of SQL Server. It is responsible for storing, processing, and retrieving data. The Database Engine consists of two main components: the Database Management System (DBMS) and the Database Engine.

The DBMS is responsible for managing the physical storage and retrieval of data. It manages the allocation and deallocation of disk space, as well as the organization of data on the disk. The Database Engine, on the other hand, manages the logical operations that are performed on the data. This includes operations like querying, indexing, and sorting data.

2. Analysis Services: Analysis Services is a component of SQL Server that enables users to analyze and model their data for better decision-making. It includes tools for data mining, data analysis, and data visualization.
3. Integration Services: Integration Services is a platform for building and deploying data integration solutions. It includes tools for extracting, transforming, and loading data between different systems.
4. Reporting Services: Reporting Services is a platform for creating and deploying reports that can be accessed by users via a web browser or a mobile device. It includes tools for designing, publishing, and managing reports.
5. Master Data Services: Master Data Services is a component of SQL Server for managing master data, which refers to the data that represents the core business entities of an organization. It includes tools for creating, managing, and governing master data.

SQL Server Tools

SQL Server comes with several tools for managing and working with data. Here are some of the key tools:

1. SQL Server Management Studio (SSMS): SSMS is a graphical user interface tool for managing SQL Server. It provides a range of features for managing databases, users, and permissions, as well as for writing and executing SQL queries.
2. SQL Server Data Tools (SSDT): SSDT is an integrated development environment for building and deploying SQL Server databases. It provides a range of features for designing database schemas, creating queries, and deploying databases.
3. SQL Server Profiler: SQL Server Profiler is a tool for monitoring and analyzing the performance of SQL Server. It allows users to capture and analyze SQL Server events, such as queries, stored procedures, and transactions.

Conclusion

SQL Server is a powerful and versatile database management system that offers a wide range of features for managing data. Its components and tools make it easy for businesses of all sizes to store, manage, and analyze their data. Whether you are a small business owner or a large enterprise, SQL Server can provide you with the tools you need to manage your data effectively.

SQL Server Basics series

Hello Dear Reader, we would be starting an SQL Server basics to Advance series and will be covering below topics .

Article 1: Introduction to SQL Server Administration

• Overview of SQL Server and its components
• Installation and setup of SQL Server
• Creating and managing databases
• User and permission management
• Backup and recovery strategies
• Monitoring and troubleshooting SQL Server

Article 2: Managing Data in SQL Server

• Data types and schema design
• Creating and managing tables, views, and indexes
• Data manipulation with SQL statements (SELECT, INSERT, UPDATE, DELETE)
• Data normalization and denormalization
• Bulk import and export of data
• Transactions and locking

Article 3: Performance Tuning in SQL Server

• Understanding query execution plans
• Identifying and resolving performance bottlenecks
• Indexing strategies and best practices
• Query optimization techniques
• Monitoring and tuning server resources (CPU, memory, disk I/O)
• Performance troubleshooting and tuning

Article 4: High Availability and Disaster Recovery in SQL Server (5000 words)

• Introduction to high availability and disaster recovery (HA/DR) concepts
• Implementing database mirroring, log shipping, and replication
• Configuring failover clustering and availability groups
• Backing up and restoring databases for disaster recovery
• Testing and maintaining HA/DR solutions
• Business continuity planning and best practices

Article 5: Advanced SQL Server Administration Topics

• Advanced security and encryption features
• Managing large databases and data warehouses
• Integration with other systems (SSIS, SSRS, Power BI)
• SQL Server in the cloud (Azure SQL Database, AWS RDS)
• Extending SQL Server with custom code (CLR, PowerShell, T-SQL)
• Performance and scalability considerations for large-scale deployments

getting started with Powershell

get started with POWERSHELL learning with best.. The invertor himself Jeffery Snover

 

 

 

using progress Bar in powershell

Hey all ,

I was looking for an way to pop up an progress BAR for an ongoing progress within an POWERSHELL script ..

figured out powershell inheritably provide an inbuilt cmdlet for doing the same .

Command ::

write-progress

for detailed help and examples on this command use below cmdlet

## get-help

  get-help write-help -full | more

 

Example ::

for ($i = 1; $i -le 100; $i++ )

{

get-process

write-progress -activity “Search in Progress” -status “$i% Complete:” -percentcomplete $i;}

 

Example for 2 bars ::

for ($i = 1; $i -le 100; $i++ )

{

get-process

write-progress -activity “Search in Progress” -status “$i% Complete:” -percentcomplete $i -CurrentOperation “outerloop” ;

for ($j = 1; $j -le 100; $j++ )

{

get-process

write-progress -Id 1 -activity “Search in Progress” -status “$j% Complete:” -percentcomplete $j -CurrentOperation “innerloop”;

}

}

 

Query Complete SQL error log in one GO !!

Hi All,

this post coming after a long time.

 

if you need to search a particular word or error code in the SQL error long,then you would have to do it one by one .

i.e .. you will proceed like below (let say for example we are looking for  connection timeout errors in Always ON setup, this could be any error or a

particular  word you are interested in looking for)

to query the current SQL error log

EXECmaster.dbo.Xp_readerrorlog 0,1,N‘A connection timeout’

0 –> current logs  | 1 –> SQL error log (also if you specify nothing in this place .. it is taken as SQL error log by default)

third parameter is something you are searching for in the error log ..

for the previous error log (ie errorlog.1) we have to use EXECmaster.dbo.Xp_readerrorlog 1,1,N‘A connection timeout’

so this becomes tedious over time .. let put this in a loop as below  …

assuming you have total of 30 previous long retained ..

use tempdb

go

CREATE TABLE #read_error_log

(

logdate DATETIME,

processinfo VARCHAR(200),

errorlogtext VARCHAR(max)

)

declare @int int= 0

while (@int <= 30)

begin

INSERT INTO #read_error_log

EXEC master.dbo.Xp_readerrorlog @int,1,N’A connection timeout’

set @int= @int+ 1

End

SELECT *

FROM  #read_error_log

ORDER BY logdateDESC

–TRUNCATE TABLE #read_error_log

–DROP TABLE #read_error_log

 

 

The above query will give you results much faster then query individual error logs ..

Refer below Links for more details on reading error logs ::

http://mssqlwiki.com/tag/sp_readerrorlog-parameters/

http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

http://strictlysql.blogspot.in/2013/05/xpreaderrorlog-in-sql-server-2012.html

Enjoy 🙂

 

 

Database Page Basics

Hi all,

i was looking for a some basics to find out to which Table a particular page belongs to ..

Found below you tube Channel which covered everything i was looking for

check out the link and subscribe to the same ..   SQLpassion by  Klaus Aschenbrenner 

 you will cover below basics if you try out the example yourself ,

1>basic Database  PAGE Structure  and how to use DBCC IND , DBCC PAGE  and DBCC TRACEON(3604)

2> how the table partition and allocation units are related each other

3> what are different types of Allocation units  i.e in row ,LOB and row over-flow data

   Please try the example as you go through the the links 

   1 

   2

3

4

Enjoy 🙂

HOW TO Set up TEST Environment for SQL SERVER 2014 CTP2 or SQL SERVER 2012 PART 2

Hi ALL,

for setting up TEST env for Server 2012 and above refer to my First post for initial details and videos from 1 to 6

remaining Videos are as below :: –>

once again CREDITS goes to metamanager  (Check out the Channel and Subscribe to the same)

Video number 7 

Video number 8 

 Video number 9

Video number 10

 

CREDITS goes to metamanager 

Thanks a lot for checking out the page .. do comment and share !!!

Enjoy

 

HOW TO Set up TEST Environment for SQL SERVER 2014 CTP2 or SQL SERVER 2012 PART 1

Hey ALL,

if you are looking forward to set-up a test server for playing around with SQL server 2014 CTP2 release or have some

concepts cleared on always ON on SQL SERVER 2012 .. Follow on ..

Below is the flow of this post ..

1> links for pre-requisite for Setting up SQL server 2014 CTP2 or SQL Server 2012 Environment

2> Video Links for Setting up SQL Server 2012 (Same concepts and set-up is applicable to SQL Server

       2014 CTP2) 

Video links are taken from metamanager  (Check out the Channel and Subscribe to the same)

Credits for Videos goes to metamanager .. Thanks a lot for sharing them … Great for the SQL communities 

Host machine specs

–>  windows 7 ,8 GB RAM,core i5 ,around 150+ GB HD (so anything close to this or higher will be great )

download below ISO Files for

windows SERVER 2012 ,

SQL server 2012

or

SQL server 2014 CTP2

i have preferred to download ISO file (you may use CAB if you prefer , ALL links are for Evaluation Editions only )

1> Windows Server 2012 –> 

http://technet.microsoft.com/en-in/evalcenter/dn205286.aspx

 

2>  SQL Server 2012 –> 

http://www.microsoft.com/en-us/download/details.aspx?id=29066

OR

3> SQL Server 2014 CTP2 –>

 http://technet.microsoft.com/en-US/evalcenter/dn205290.aspx

 4>  VMWARE workstation or Hyper-V is required to set-up the Virtual environment

  https://my.vmware.com/web/vmware/info/slug/desktop_end_user_computing/vmware_workstation/10_0

                                                                                               OR

 –> if you are interested in trying out Hyper-V then check out This links  (it is also  present as a feature in Server 2012)

         Once you are done with downloading the OS,SQL Server and VMware (or Hyper-V)

              Follow below Videos to Set-up your Env ..

                                                                             1> 

 

                                                                            2> 

 

                                                                           3> 

 

 

                                                                         4>

 

 

                                                                        5>

 

 

                                                                     6>

 

 

REST of the Videos .. i will share in second post ..

once again CREDITS goes to metamanager  (Check out the Channel and Subscribe to the same)

Enjoy 🙂

Rename multiple FILES using POWERSHELL

Requirement ::

I needed to upload my Educational certificates onto a particular websites ..

after i was done with scanning my docs .. i never realised that the sites wont accept PDF or any other fromat Documents

which had special Characters in the name Exxmple –> ( ,&,*,@,$ ETC …

in my case the Documents were named Document(1).PDF 

so i needed to get there name changed to just Document1.PDF 

aligned snapshot is the what my docs looked

POWERSHELL SCRIPT ::

to change the names of the PDF files .. used POWERGUI and used below command to set my location to the path which

contained the PDF files

SL stands for SET LOCATION and is equvalent to CD

sl ‘D:\GS\DOCS\TEST_scancopy’

then i used below command to get a count of files that had round brackets [ ( )  ] in their names

get-childitem -Filter ‘*(*’ | measure

this gave me idea of how many docs need to renamed …

i used a while loop to run for 29 times … to ensure all the PDF files

having *(* present in their name are set to required name ..

The rename-item was used to set the names of PDF files .

below is PS Script i used to rename the docs … as i have around 29 docs .. i have used 30 as the no. of times the loop will run through

___________________SCRIPT  STARTs __________

$int = 1

Write-Host $int

while ($int -ne 30)
{
Write-Host ‘within while loop’
Write-Host $int

$var = ‘Document’+ $int.ToString()+’.PDF’

# the names to which docs will be set

get-childitem -Filter ‘*(*’ | select -first 1 | rename-item -NewName $var

Write-Host ‘renamed to ‘ $var

$int ++

}

___________________SCRIPT  ENDs __________

The SCRIPT ran fine ..

you may use similar approach to tackle .. any other requirement 

specific to your need  ENJOY 🙂 

 

Running DBCC CHECKDB on all databases at once !!!

Issue ::

some times it so happens .. that the  storage assigned to SQL servers may take a sudden

unexpected reboot .. and in those moments we may have LOST WRITES in

          databases .. which can cause them to be marked as suspect or causing 

          issues while accessing objects … 

 

in those moments we have to ensure consistancy and allocation across all DBs

are intact .. running DBCC CheckDB manually on each and every option is time

consuming (plus multiple windows  are to be opened)

SCRIPT ::  below script will help us run DBCC checkDB on all DBs once we have count of all DBs present on our instance ..  (the other option you have to achieve the same is to use CHECKDB job .. but with below script you will be able to view the errors in Query analyser itself)

 

1> use sp_helpdb .. to check total number of databases present on your instance …

according to the total number of DBs .. use dbid >= * and dbid <= * to cover all DBs

—Script to Run in different windows

—— by commenting and dbid >= * and dbid <= * part in the where clause

 

 

—————————————-SCRIPT START ————————————————-

DECLARE @name VARCHAR(50) — database name
DECLARE @SQL VARCHAR(256) — SQL command

 

 

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘tempdb’)
and dbid >= 1 and dbid <= 20 —- window 1
–and dbid >= 21 and dbid <= 50 —- uncomment and run from QA window 2
–and dbid >= 51 and dbid <= 100 —- uncomment this and run from QA window 3
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

 

 

WHILE @@FETCH_STATUS = 0
BEGIN
–SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
—BACKUP DATABASE @name TO DISK = @fileName

 

 

set @SQL = ‘DBCC CHECKDB’ + ‘ ‘ + ‘(‘+@name + ‘)’
print @SQL

exec (@SQL)

 

 

FETCH NEXT FROM db_cursor INTO @name
END

 

 

CLOSE db_cursor
DEALLOCATE db_cursor

 

 

—————————————-SCRIPT END————————————————-

 

 

you should see the output similar to above ..

go throught the errors and see if Allocation or integrity errors are reported for any DB …

accordingly use Repair_REBUILD or REPAIR_ALLOW_DATA_LOSS of DBCC CheckDB ..

alway prefer to use lastest Good backup over using above repair option !!!!

ENJOY 🙂