RDBMS: List of keys in RDBMS in SQL server

Details

Written by Ranjeet

Being a RDMBS guys, it’s always been a mystery remembering all the keys. Although you must be knowing these but if someone ask you to list out all the keys it is little difficult to summarize all those at once. So, today post is related to listing all the keys. However I am listing all the RDBMS keys but not going to write its definition and its concept. You can follow my RDBMS related article to get details about these keys.

Below is the list of Keys:

• Primary Key
• Unique Key
• Foreign key
• Candidate key
• Alternate key
• Secondary key
• Composite Key
• Compound key

  Register to read more...

  0 Comments

ALWAYS ON: automatic failover risk vs benefit in disaster recovery in SQL server

Details

Written by Ranjeet

While designing HA and DR solution. Client always stuck at the point where DC to DC should have automatic failover or manual failover. For a DBA, It’s becomes challenging task to convince them that let’s go for manual or let’s go for automatic. This post is especially for those DBAs who wants to have pointers ready to convince client to choose either one. I am trying to explain these in risk vs benefits format.

Suppose DBA want to go manual failover from DC to DC failover and automatic failover from Node to Node with DC in primary data center.

Risk involved setting up automatic failover

• Possible failover can happens in normal network glitch. Which would impact overall production availability
• Production database performance impact
• In case of DC network slowness, production database transaction would impact and result in timeout

Benefits setting up automatic failover

• No manual intervention required in case of certain DR scenario

0 Comments

ALWAYS ON: 3 nodes multi-site HA and DR architectural solution in SQL server

Details

Written by Ranjeet

Today I chosen to write something on 3 nodes cluster. Actually recently and in past I have been involved in designing two nodes and 3 nodes cluster. In this post, I will focus on 3 node cluster and will try to show what the industry best practice architectural solution is. I will keep nodes in two different subnet which is also called geo-cluster, multi-subnet cluster or DR cluster.

I assumes you have high level of understanding about below items

• WSFC cluster
• FCI SQL Cluster Instance
• Standalone SQL Instance
• Availability group
• Availability group Listener
• About different Quorum Model
• File Server and File Share
• What does mean by High Availability and
• What does mean by Disaster Recovery

Register to read more...

0 Comments

TABLE: how to get space from deleted rows in sql server

Details

Written by Ranjeet

Recently I performed historical data purging on large scale. It removed almost 80% of the data it had. After completing purge operation, we noticed that space occupied by database is still same. Even used and free space. Now question is where the space is now, how SQL server did reused the space, can we get space back, did we purging in right way etc.

In order to perform purging we used DELETE command to remove the historical data. So, since we are using DELETE, we know it won’t release the space. Whatever space is free will be re-used later one.

Then what is the solution to get space back. We can’t use TRUNCATE as it will remove the complete data. Solution here is do the clustered index rebuild. Once you do Clustered Index rebuild it organize data physically and release the free space. You can perform rebuild index on whole database or you can point out all the big table which was participated in purging and then perform clustered index rebuild on only those tables. Below script can use to perform rebuild index on individual tables.

ALTERINDEX [IndexName] ON [dbo].[TableNme]

REBUILDPARTITION=ALL WITH (PAD_INDEX  =OFF,STATISTICS_NORECOMPUTE  =OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON,ONLINE=OFF,SORT_IN_TEMPDB=OFF)

GO

0 Comments

SQL SERVER: SQL Server post-installation Configuration settings best practice checklist

Details

Written by Ranjeet

Being a DBA it is very important to keep the best practice checklist of post-installation stuff. Once you installed SQL Server with proper planning of pre-installation and installation checklist. There are still a lot left to do after installation complete. As a best practice we can follow below post installation checklist.

SQL Server post Installation Configuration best practice

Instance Level configuration best practice

1. Test the SQL Server connectivity
2. Check the network connectivity and make sure all the required network protocol are enabled
3. Check the latest patches installed
4. Memory configuration
5. Configure TEMPDB datafile

   Register to read more...

   0 Comments

SQL SERVER: SQL Server installation Configuration settings best practice checklist

Details

Written by Ranjeet

Being a DBA it is very important to keep the best practice checklist of installation stuff. While installing SQL Server, it’s ask for n number of parameters value. Most of the times we just leave as default values without understanding its future impact. Here I am trying to list all those parameters which we need to understand and change accordingly keeping in mind for future impact. These settings are for standalone SQL Server instance. Although we can use pretty much same thing for clustered environment too.

SQL Server Installation Configuration settings best practice

1. Choose the only features which is required to get installed. Don’t select all as a normal practice
2. Change the “Shared features directory” and “Shared features directory (x86)” to D drive
3. Give the Instance name you want to install
4. Change the instance root directory path from default C drive to D drive
5. Pass the service account credential and avoid installing by default your account name

   Register to read more...

   0 Comments

SQL SERVER: SQL Server pre-installation Configuration best practice checklist

Details

Written by Ranjeet

Being a DBA it is very important to keep the checklist of pre installation stuff. I tried to list out all the required pre-installation checklist for installing standalone SQL Server. Later on I will add checklist to install on cluster environment.

SQL Server pre-Installation Configuration best practice

1. Window Server is configured as fresh and updated with latest patch
2. Select proper number of process and server keeping in mind of core based licensing
3. Make sure window firewall the properly configured and all the port are open as required
4. Make sure anti-virus is installed
5. Choose the SQL Server port other than default 1433
6. Check SPN

   Register to read more...

   0 Comments

SQL SERVER: how to change the default location of datafile logfile and backup file in SQL SERVER

Details

Written by Ranjeet

It is very common mistake when we forget to change the default datafile logfile and backup location while installing SQL Server. Below the step you can follow to change the default file location properties.

1. Open the management studio
2. Connect to SQL Server instance
3. Right click on instance and select properties
4. You will get a dialog box. Select “Database Settings” from left pan.
5. Change the new datafile logfile and backup file location in respective text box. Below is on screen shot

   Register to read more...

   0 Comments

JOB: how to schedule the SQL job for every last Sunday of month

Details

Written by Ranjeet

I wrote the data purging script and tested across the environment. Now we need to schedule this job to run every last Sunday of the month. First thought was how is it possible? How the SQL Server will recognize that it’s last Sunday and etc.

When I did some R&D then I found the very easy way to do this. Let me step out here. I hope it will save your time.

1. Right click on the job
2. Select new job
3. Select Schedule from left pan
4. Click on New button
5. Give the schedule name
6. Choose monthly on occurs column
7. Then choose ‘The’ radio button
8. Then choose ‘Last’ and then ‘Sunday’

   Register to read more...

   0 Comments

SQL SERVER: Database maintenance items checklist in SQL Server

Details

Written by Ranjeet

From Long time back, I was planning to summarize all the database items/checklist at one place to help DBA’s who need to setup in their environment. Every database environment must have all kind of required database maintenance items/checklist configured and also related TSG must be updated. In this article I will try to list out all those database maintenance items/checklists. Follow my subsequent articles to have every item’s description and get full scripts. Let me categories the items/checklists. It’s totally database administration based not any application based.  

Object Level maintenance

1. Index maintenance
2. Statistics maintenance
3. Verify constraints
4. Verify new/alerted Index

Register to read more...

0 Comments