Category: SQL Server

AWS SQL Server RDS Native Backup Restore

Steps to Restore .bak SQL Server backup file to Amazon SQL Server RDS
Step by Step Guide to backup and restore SQL Server backups on AWS RDS through S3 Buckets.

Dynamic SQL Search Stored Procedure

Dynamic SQL Search Stored Procedure
This also includes how to safeguard against SQL Injection by escaping single quotes through SQL function.

Caching SQL Server Tables After Restart

SQL Server Cached Pages and ways to bring data pages to memory
We noticed considerable latency in stored procedure execution after restarting SQL Server Instance which was triggered by accessing data from disks instead of memory. So we ended up writing a simple script to bring data pages to memory. This helped us to avoid first touch penalty after restart in production environment.

Dynamically Restore SQL Server Database from Backup

Dynamically Restore SQL Server Database from Backup We always get requests to restore databases from production to lab environment.This script dynamically restores the backup file and moves the data and log files to default locations.

Launching AWS SQL Server RDS

Learning from Launching AWS SQL Server RDS
We are evaluating options to migrate our read/write heavy production SQL Server database to amazon SQL Server RDS. We have pretty high throughput needs for few hours a day for few months in a year ,which is mission critical for our business success. We are evaluating pros and cons of moving to amazon RDS with provisioned IOPS. Current hosted provider doesn’t offer on-demand scaling solution .

Change SQL Server Authentication login password

We have numerous applications using SQL Login accounts . Simple powershell scripts saves time for rotating passwords for SQL Logins.

SQL Server Recursion / CTE

Simple Employee data hierarchy to illustrate recursion through traditional while loop as well as Common Table Expressions. ( CTE )

AWS RDS vs Azure vs Managed Services for SQL Server

We are evaluating pros and cons of different hosting solutions for SQL Server which best suits our business needs.
Our plan is to evaluate AWS SQL Server RDS, Azure RDS , Managed solutions from hosting provider.
Evaluate each option in these categories.
1. Performance and Reliability
2. Ability to scale up during peak loads
3. Cost ( Based on Network , Storage, Memory and CPU )
4. Operations Efficiency
5. Compliance

SQL Server – Set Max Memory

SQL Server – Set Max Memory MSDN Link on Changing Max Memory

SQL Server Internal Versions

SQL Server Internal Versions SQL Server Versions Database Internal Versions and Compatibility Levels Useful Query

Remove Procedure Cache and Reset Wait Stats

Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance

Flush the cache and suppress the regular completion message

Remove all elements from the plan cache for a

Common Sense RDBMS Patterns

Common Sense Relational and Development Patterns : RDBMS_Best_Practices Great Articles on SQL Magazine Talk: SQL Magazine Best Practices Part One SQL Magazine Best Practices Part Two RBAR – If you are a relational database professional, you know RBAR ( Row

Configurations : Web Config or Database?

Configurations : Web Config or Database? Web Config: Database Connection Strings. OfCourse. You can’t connect without that details. Changes you want trigger Application Pool refresh. Things need to be available when the database is unavailable. (such as a list of

SQL Server System Info

SQL Server System Info This script provides SQL Server instance level configurations. GitHub Gist : https://gist.github.com/I90Runner/7bc31a10897a2168816073db781d5e77

Results from the Above Query

SQL Server :Transfer Logins from One Instance to Another Instance

Transfer Logins from One Instance to Another Instance

References Microsoft KB Article : Transfer Logins SQL 2012 Transfer Logins Older Versions SQL Skills Article : https://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/

SQL Server AlwaysOn Availability Groups

SQL Server Always on Availability Related Queries: How do I switch Primary / Secondary on Switch Always on Availability Groups ( Swap Primary / Secondary ): When the automatic failover is setup , the Primary / Secondary gets swapped during

SQL Server Mirroring

SQL Server Mirroring Related Queries: How do I swap Primary and Secondary on Mirroring Databases ? When the automatic failover is setup , the Primary / Secondary gets swapped during maintenance tasks. To swap back the Primary/Secondary , connect to the

SQL Server Hardware Selection

SQL Server Hardware Selection , licensing cost and BenchMark Tools.

TempDB Contention

TempDB contention: TempDB plays very crucial in well performing SQL Server engine. These are few simple design decisions , which helps to avoid the performance bottlenecks in the long run. As a general rule, if the number of logical processors is

Concurrency and Storage Needs

Concurrency and Storage Needs : Requirements: We operate on K-12 Online Summative Market. Our Needs are: When Kids log-in to take the test, they should be able to login without any issues. Should be able to store their responses and

Top