Latest SQL DBA Interview Questions and Answers

Latest SQL DBA Interview Questions and AnswersThe post Latest SQL DBA Interview Questions and Answers takes you through the latest SQL DBA interview experiences shared by blog followers. You can also share your interview experience and we can provide you the answers:

Q. Can we perform a log backup with COPY ONLY option?

Ans:

Yes Of course we can perform Copy Only log backups

Q. What are the marked transactions in SQL Server?

Ans:

When we are dealing with related databases which means there is a dependency between databases and we need to establish a recovery strategy for all databases to the same consistent phase. But with this approach we may lose the recently committed transactions. Marking transaction logs is beneficial when you are dealing with multiple databases and want to restore all of them to the same point in time recovery.

This is used in rare cases and suitable for TFS because when there is disaster ‘occurs we need our data back in all databases to the same consistent manner, if you don’t mark the T-Logs and restore using the normal point in time recovery it might get our data back but with inconsistency between databases. For example I have created a new branch in TFS for new development work and and somebody checking and modifying code this total activities are recorded in multiple databases so when we want to restore it back we should get exactly the same consistent point. Have a look at here:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/use-marked-transactions-to-recover-related-databases-consistently

Q. Why Primary key is mandate for Transaction Replication?

Ans: Continue reading

Posted in Interview Q&A, SQL Server DBA | Tagged , , , , , , | Leave a comment

Learning Microsoft Power BI Lesson 4

Learning Microsoft Power BI Lesson 4Learning Microsoft Power BI Lesson 4 – Exploring Power BI Service

The post “Learning Microsoft Power BI Lesson 4” takes you through the over view and interacting with Power BI Service. We have created an account at Office 365 and using to log into Power BI Service:

Log into Power BI service: https://powerbi.microsoft.com/en-us/

Learning Microsoft Power BI Lesson 4 Exploring Power BI Service

We’ll explorer Power BI service with the Sample Datasets available:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

The moment you connect to the “Sales and Marketing” dataset, it connects to the sample dataset and creates a Dashboard and report with a set of visuals as shown in below:

Learning Microsoft Power BI Lesson 4

We can observe various things from below Screen:

  1. Hide / View Navigation Pane
  2. Available Workspaces
  3. Enter Full Screen Mode
  4. Viewing the report in Web view mode
  5. Viewing the report in Mobile view mode

Learning Microsoft Power BI Lesson 4

Let’s view the report in mobile mode:

Learning Microsoft Power BI Lesson 4

Power BI automatically creates Dashboard and Report using the sample datasets:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

From the default report created from sample dataset we’ll prepare a new dashboard by adding specific visuals from each page:

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

Go back to My “Work Space” and from Dashboards we can see the newly created Dashboard

Learning Microsoft Power BI Lesson 4 Learning Microsoft Power BI Lesson 4

Summary:

  • We have interacted with the Power BI Service interface
  • Connected to sample dataset and observed how reports and dashboards are created
  • Created “MyFirst_Dashboard” using the visuals / tiles from the existing report

Power BI Lesson 1

Power BI Lesson 2

Power BI Lesson 3

Power BI Introduction

Posted in MSBI, Power BI, SQL Development | Tagged , , , , , , , | Leave a comment

Learning Microsoft Power BI Lesson 3

Learning Microsoft Power BI Lesson 3

Learning Microsoft Power BI Lesson 3 – Exploring Power BI Desktop

The post “Learning Microsoft Power BI Lesson 3” takes you through the overview and creating the first report using Power BI desktop. Here we have seen how to install the Power BI Desktop on Windows. Now we’ll open it and explore the features:

Power BI Desktop:

Power BI Desktop has 6 main areas:

  1. Toolbar: It contains a set of tools which are used to get data, develop and publishing the reports
  2. Report View / Canvas: Here we actually give life to data using various visualizations
  3. Ribbon: Showcase the common tasks which are associated with the reports and visualizations
  4. Pages: Allows us to add, remove pages in a report
  5. Visualizations: Where we can add, change visualizations, customize colors or axes, apply filters, drag fields etc.
  6. Fields: Where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane

 Learning Microsoft Power BI Lesson 3

Getting Data into Power BI:

We’ll see how to load data from a source to Power BI Desktop. Power BI is a data centric application thereof we need to focus on providing the clean data as per the business requirement. Currently Power BI supports 70 + data sources and these are categorized into 5 types:

File: Excel, Text, CSV, XML, JSON, Folder, SharePoint Folder

Database: Ex: SQL Server, Oracle, DB2, PostgreSQL, SAP, MySQL, Amazon Redshift, Impala etc.

Azure: Ex: Azure SQL Database, Blog Storage, Azure Table Service etc.

Online Services: Ex: Dynamics 365, Sales Force / Reports, Facebook, GitHub etc.

Other: Ex: R Script, OLEDB, ODBC, Hadoop File, Active Directory etc.

I would suggest download and attach/restore [AdventureWorksDW2014]

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

Select SQL Server Database and connect it:

Learning Microsoft Power BI Lesson 3

Now we have to select tables to import from SQL Server to Power BI. We’ll choose two tables from database [AdventureWorksDW2014]:

[DimProduct]: It stores product properties / metadata that might include name, color, height, weight, stock, price etc.

[FactInternetSales]: It stores product sales information that might include sale amount, tax amount, date info, discount info etc.

 Note: If you are new to dimensions and facts don’t worry about it, just google it and see what is a FACT and Dimension. To simplify fact indicates numbers / measures and Dimension gives a meaning to fact using its properties / metadata. Ex: If I say 5 Cameras it’s just a measure which doesn’t have any meaning now if I say Mr.Chris has purchased 5 EOS cameras from Canada e-store on 14-Sep-17 gives a proper meaning. Here 5 is the FACT and dimensions are Customer (Chris), Product (Camera details), Country (Canada), Store (e-store) and Date.

Import above tables into Power BI:

Just try to go with the flow, we are just exploring the Power BI desktop for the first time and not going to try all the options in one go.

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

Go back to the Report TAB and now we are going to design a simple report:

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

Let’s have a look at Visual (Clustered Column Chart) Properties:

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

We’ll use “Fields” and “Format” tabs and apply few customization:

The chart is showing the Sales Amount based on the “ShippingDate”. It’s automatically groups data based on “Year”, “Quarter”, “Month” and “Day”. We’ll remove Month and Day as we just required Year and Quarter.

Learning Microsoft Power BI Lesson 3

Now change the Chart data colors, Format the Chart Title, X-Axis, Y-Axis text size etc. Select the Chart and go to Format tab:

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

Finally add a Text box to the report also change the Page name to MyFirstReport:

Learning Microsoft Power BI Lesson 3 Learning Microsoft Power BI Lesson 3

Summary:

  • Started interacting with Power BI Desktop
  • Seen various parts of Power BI Desktop
  • Loading data from SQL Server to Power BI Desktop
  • Creating a simple report with a single visualization
  • Applied few formations to report
  • You can play with more options and get yourself free with the Power BI environment

In Next lesson we’ll start interacting with Power BI Cloud based Service

Power BI Lesson 1

Power BI Lesson 2

Power BI Introduction

Posted in MSBI, Power BI, SQL Development | Tagged , , , , , , , , | Leave a comment