In my previous post Introduction: SQL Azure ; we had a brief introduction of SQL Azure and how this is different from SQL Server. This blog post will illustrate “How to create database” in SQL Azure. To create a database in SQL Azure (Evolution Edition), all you need is a windows live ID or windows passport. To activate your windows Azure, you need to sign up at https://windows.azure.com . Login with your window live account or with email registered at windows passport. Once logged-i...
Read More
Completed-Advanced SQL Server 2008 & Performance Tuning workshop in Pune 2011 Hi Friends, I like writing about my trainings – there is so much to learn in every training assignment. I get to meet new minds, new personalities, get to know their SQL stories, their issues, remedies, tips, tricks, techniques and bunch of little things! I blogged about my last training here that I completed in Bangalore: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/321/completed-sql-server-b...
It is always a big challenge to maintain application’s performance optimum. Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report the slowness in the application, only then DBA’s/Developers jump in and begin the optimization exercise. Ideally, DBA’s/Developers supporting the application should be the first in identifying the performance problems and should proactively optimize the faulty/poor code. Well to acheive th...
SQL Server – Merry Christmas – SQL Server style Hi Friends, Merry Christmas to all our readers! Here is a small Christmas query from my friend, Andrea Benedetti: /* ******************** Happy SQL Christmas! ******************** Andrea Benedetti, SQL Server MVP Twitter: @anbenedetti Mail: abenedetti@absistemi.it */ SET NOCOUNT ON; /* please choose the level of the tree... :-) */ DECLARE @level smallint = 10; DECLARE @i tinyint = 1; DECLARE @Offset smallint = 10; DECLARE @x1 smallint =...
Scenario: Database sizing is one of the most critical tasks for any DBA. One of the DBA’s tasks is to also ensure optimum performance. Database/table size is one of the key factors impacting the performance. Hence, it becomes essential to keep close eye on data volumes entering and departing the databases. Solution: I am sure most of the DBA’s would be aware of Microsoft’s undocumented SP’s. ‘sp_spaceused’ in one of them and proves to be very handy for DBA&rsq...
Question - How to migrate all the reports and data sources deployed on the old server to the new server? Scenario – Our Company is going for the new server setup by discarding the old servers and the Reporting Services machine is one of them. This usually happens may be to change or upgrade the underlying hardware or to upgrade software like moving from Windows Server 2003 to 2008, from SQL Server 2005 to 2008 etc. As a DBA, a task is assigned to me to setup the new Reporting Server same a...
SQL Azure is part of Microsoft Azure Service Platform which provides data storage/manipulations capabilities to the platform. SQL Azure uses a special version of SQL Server as backend. This provides two editions of database namely Business Edition and Web Edition. Maximum database size in Business edition can be of 150 GB and 5 GB for Web edition. Being a cloud based service; it provides high availability by storing multiple copies of databases and in addition that it provides elastic scale and ...
Hi Friends, Last week, I promised to publish an article on How to Implement Contained Databases but I couldn’t, so here it is: Step-by-Step guide to Implement Contained Databases This article will explain: How to create Partially Contained Databases. How to create Users without Login (Users with Passwords) How to connect to SQL Database without a Login You can read the article here: http://www.sqlservergeeks.com/articles/sql-server-bi/82/step-by-step-guide-to-implement-contained-databases-...
Question of the day: What all changes require a restart of SQL Service? Let’s say you get a request to change a SQL Server setting and you don’t know whether it requires a restart of SQL Service to take into affect or not, now what will you do? You’ve left with following options: Ask someone you know (You think this person must know the answer but what if he is also guessing?) Search for it, now a days you find everything on internet (but you must hit the right keywords and som...
SQL Server-Capturing Deadlocks using Extended Events Hi Friends, I was neck-busy in an assignment so could not write for sometime. Sometime back I wrote an article on monitoring page-splits using extended events. You can browse that article here: : http://sqlservergeeks.com/articles/sql-server-bi/76/sql-server-monitoring-page-splits-using-extended-events Today I want to talk about capturing deadlocks using Extended Events, but before I you read this blog post further, I suggest you first go thro...
SQL Server 2012 Denali CTP3 SSIS – Sending files using FTP Task Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In the last post, we had a short walkthrough for receiving files from an SFTP server using SSIS Execute Process Task. You can find the published content here In this post, we shall send files or upload files to a remote server using FTP. ...
SQL Server 2012 Denali CTP3 SSIS – Project Connection Bug and Resolution Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. If you have worked with SQL Server Integration Service in Denali CTP3, there is a potential bug in the code. This post will showcase a workaround to avoid this bug. I have decided to divide this post in two parts: Replicate the b...
What is a contained database? A contained database is a database which includes all the required settings, metadata and operates in isolation from the SQL Server Database Engine. In other words it has no functional dependency on SQL Server Instance be it Login, collation setting or metadata info. The most popular feature being, user connecting to the database without having a Login at SQL Server Instance level; means there is no login registered for this user in Master DB. It’s very easy t...
SFTP with SSIS Execute Process Task Hello, SFTP, or secure FTP, is a program that uses SSH to transfer files. Unlike standard FTP, it encrypts both commands and data, preventing passwords and sensitive information from being transmitted in the clear over the network. It is functionally similar to FTP, but because it uses a different protocol, you can't use a standard FTP client to talk to an SFTP server, nor can you connect to an FTP server with a client that supports only SFTP ( Source: Knowled...
Hi Friends, A few days back, I got a backup failure alert regarding one of our most critical SQL Instance. I quickly connected to our jump-box and took a session of the server to diagnose further. From the job history I concluded that I need to modify the maintenance plan in order to fix it, so I located the respective maintenance plan (we have many maintenance plans on this instance) and when I double clicked I was welcomed with a strange unique error. I was using SQL Server 2005 SP2 on this se...
Hi Friends, I know most of you will not like it, but Now SQL Server Certification Exams will also come along with an Expiry Date. Microsoft is changing a few Certification Exam policies and from SQL 2012 Pro Exam launch you need to reappear (in other words you need to do recertification) to keep it under active status. This means: you need to plan for more money in your yearly education budget, More Stress and you need to be always up-to-date to be active and eligible for Certified positions. Re...
SQL Server 2012 Denali CTP3 Known Bug and Resolution Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. If you have worked with tabular projects in Denali CTP3, there is a potential bug in the code. This post will showcase a workaround to avoid this bug. If you wish to see any feature like how to create a tabular structure and additional details, I suggest ...
Hi Friends, This is a common question I face in many of my classes and consulting assignments? The answer is NO. Indexes are B-Tree structures (balanced tree) where navigation/searching happens from top to bottom (root to leaf level) and building the index happens from bottom to top (leaf level to root level). Whenever you read about Indexes and B-Tree structures, you will always come across the mention of the root page; making you believe that the root page always exists irrespective of the amo...
BI Semantic Model and Reporting Part 2 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In this post, let’s create couple of Roles using the Role Manager If you wish to see any feature like how to create a tabular structure and additional details, I suggest you follow this link here and also here . This should help you kick start on Tabular Models. ...
Hi Friends, There is a good news, SQL Server 2012 Express LocalDB RC0 is Available Now. LocalDB is a new version of SQL Server Express created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. By using LocalDB, developers don’t have to manage a full instance of SQL Server Express. This is a dedicated version of SQL Express for developers....
BI Semantic Model and Reporting Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In this post, let’s create a simple no frills, no fancy excel report. Down the series, I shall introduce you to Perspectives, Roles and many associated features for an elegant Self Service BI experience. If you wish to see any feature like how to create a tabular...
Password audit is a must for all SQL Servers. Weak or Blank passwords are invitation to trouble and such type of passwords may result in security breach. Everybody knows the importance of Strong and Complex password but as they say it’s always easy to preach but difficult to practice, we tend to make use of weak and sometimes blank passwords. Now the question arises how to get rid of these. Regular Password Audits is the solution. As a SQL DBA we must do a regular audit and remind the user...
Marriage Invitation CREATE PROCEDURE MyMarriage @ BrideGroom Char(NotBad), @Bride Char(Good) AS SELECT Bride FROM AndhraBrides WHERE FatherInLaw = 'Millionaire' AND CarCount > 2 AND HouseStatus ='TwoStoreyed' AND BrideEduStatus='PGorAbove' HavingBrothers='NO' AND HavingSisters ='No' AND AllowRelocate ='YES' AND SELECT Gold, Cash, Car, BankBalance FROM FatherInLaw UPDATE MyBankAccout SET MyBal = MyBal + FatherinLawBal UPDATE MyLocker SET MyLockerContents = MyLockerContents + FatherinLawGold IN...
Hi Friends, This is a second part in the series "Considerations for Time Dimension". In the Part 1 , we have briefly discussed on the two types of the Time Dimension Structures i.e. Regular time dimension and Server time dimension. We have also started with the first out of the three creation methods which is "Use an existing table". In this post we will be discussing on the next options which enables generating a time table if it is not already available in the data warehouse. Generate a time t...
Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 14 new built-in functions. In addition to the 14 new functions, one existing function has been changed. I’ve published an article to help you understand how to use new built-in functions, New Built-in Functions in SQL Server 2012 Denali you can read this article @ http://www.sqlservergeeks.com/articles/sql-server-bi/80/new-built-in-functions-in-sql-server-2012-denali Do leave us a comment. If you liked the posts, do like us on...