Can I setup replication with an availability group as the subscriber?

Published On: 2019-09-28By:

Yes, absolutely. In order to do this, you need to configure the replication to use the Availability Group listener as the subscriber.  That way, the database that you’re using as the subscriber will follow the AG.  After replication creates the database, you will need to add it to the AG, as replication won’t do that for you.  After that, it’s just normal SQL Server Replication.

If you want to, you can replicate from the same availability group to another database on that same availability group.

Denny

The post Can I setup replication with an availability group as the subscriber? appeared first on SQL Server with Mr. Denny.


Contact the Author | Contact DCAC

HammerDB for Azure SQL DB

Published On: 2019-09-27By:

Bench marking your environment is an important step when introducing new hardware, which is accomplished by running a test workload against the hardware.    There are multiple ways to accomplish this to get  SQL Server performance data  One of these methods is using HammerDB, which is a free tool that provides TPC standard bench marking metrics for multiple database systems, including Microsoft SQL Server.  These metrics are an industry standard and are defined by the Transaction Processing Performance Council (TPC).  The results from bench marking will help you to ensure that the new infrastructure will be able to support the expected workload.

Azure introduces ways to quickly implement new hardware.  However, if the Azure environment isn’t setup correctly, you can introduce issues that could potentially degrade performance.  Thankfully, HammerDB is Azure aware which allows you to easily benchmark your cloud environment.

Let’s see how to configure HammerDB for Azure.

HammerDB

HammerDB is open source so you can download it from here.  It’s available with a Windows distribution or Linux.  For our purposes, I will showcase the Windows version.  Go ahead and install it with the default options.   Running the application requires launching a batch file as it does not include an actual EXE file.  If you accepted the defaults, youshould now have a C:\Program Files\HammerDB-3.2 directory.  Within the directory, there should be a HammerDB.bat file.  This will launch the UI for the application.  You can create a shortcut to this batch file on your desktop for ease of future use.

Go ahead and launch the hammerdb batch file.  Once the UI has been initialized, on the left hand menu tree, double click on the  SQL Server option.   In the resulting Benchmark Options dialog window, select SQL  Server and you can leave the default TPC-C option.

Click Ok.  You’ll get a confirmation window, just click OK again.  Now you’ll notice that under SQL Server there is a TPC-C tree that you can expand.   Expand the tree.

 

For the purpose of this blog post I’m only going to focus on how to configure HammerDB to connect and utilize Azure SQL DB.  I will let you play around with the other configuration settings or will blog about those at a later point in time.   Expand the Schema Build branch.  You will see an Option and Build selection.   Double click on Options

In the resulting window, you can see that there are some parameters that need to be supplied.

First, enter the name of the SQL Server.  Since we want to go to an Azure SQL DB, this is the name of the server that will host the TPCC database.    In this case, I have a demo server, sqldbdemo-east.database.windows.net available, which resides in the East region.

Next, select the Azure check box.

Thirdly, you might have to change the version of ODBC driver.  In my case, I’ve got the latest version of SSMS installed so ODBC Driver 17 for SQL Server is what I needed to use.  You can find this by looking in the ODBC Data Source Administrator if you need to find it.  If you need a different ODBC driver, you can find them here.

Finally, supply your credentials.  In this case I am use SQL Server authentication, so I supplied the appropriate user name and password.

Click Ok.  Now HammerDB is configured to work in conjunction with Microsoft Azure.  We are just about ready to build the schema for the database that will be used to perform the benchmarking.

There’s one catch, however.   The catch is that you have to create the shell of the database first.   The schema build process will not create the database if it does not currently exist.   You can easily go to the server and execute a create database statement.

CREATE DATABASE [tpcc];
GO

Now that the database shell is present, we can build the schema into the shell.  You can accomplish this either by double clicking “Build” under Options or clicking on the Build button in the toolbar.

Start the schema build.  It’ll prompt you for an OK

 

The build process will also load the data required for the actual benchmarking process.  This process could take a few minutes depending on which service tier your Azure SQL DB is sitting on.

Once it has completed, we can confirm that the schema and data are present.

Now we can commence bench marking our Azure environment!

Summary

Bench marking is a process that doesn’t occur as frequently as it probably should.  However, tools like HammerDB continue to evolve to match pace with cloud technology to help ensure we have means to do this.   Even if you are moving to the cloud, make sure to do your due diligence and benchmark things.  You might be surprised with the results..

© 2019, John Morehouse. All rights reserved.


Contact the Author | Contact DCAC

Power BI for Communication and Marketing

Published On: 2019-09-26By:

We often focus on deep analysis and insights generated by machine learning when we talk about Power BI these days because it’s super cool and very fancy. But I think it’s important to remember that you can also use Power BI for simple communication of data. As humans, we are hard wired to process visual information faster than text alone. And it’s often more efficient and concise to convey information in a visual rather than text. We can show in one image what it takes paragraphs to describe. Outside of historical analysis and operational reporting, we can use data visualizations as an engaging way to convey simple facts in a communication or marketing effort. Many people do this with infographics.

Visualization can make simple numbers and facts more memorable and engaging. I enjoy using Power BI for this marketing communication purpose, so I made a report about the people I work with at Denny Cherry & Associates Consulting. I knew most of them before I started working at DCAC, so I was already aware that they were exceptional. But now you can see it, too, with our new Power BI report located at https://www.dcac.com/about-us/learn-more-about-us.

Screenshot of the home page of our About DCAC Power BI report

Data is some people’s preferred language. We can use it to discuss specific points as well as broader trends and comparisons. I have a sticker on my laptop that says “Please Talk Data To Me” for a reason. I can make sweeping statements about how my team is full of accomplished consultants, speakers, and authors. But it is much more impactful when you see the numbers: 27 Microsoft MVP awards and 8 VMWare VExpert awards over the years, a combined 113 years of experience with SQL Server, 86% of us having been a user group leader.

My Surface Pro tablet with a “Please Talk Data To Me” sticker on it

And because Power BI is interactive, you get to interact with my report, choosing which categories you want to learn more about. There is also a little guessing game built into the report. (Although there is currently a Power BI bug that I had to work around, so the answers are limited to values 1-800 and 2001-2300. But that is a blog post for another time, and for now you get a hint as to possible answers.) If I had just told you these stats, you might have listened, but you probably would have tuned out.

Our team even had fun browsing the report, seeing our collective achievements, and guessing who provided which answers.

Building the report

It was quick and easy to use Microsoft Forms to gather the data and then dump the responses to Excel. From there, I made a quick Power BI data model and put together my visualizations. The report is embedded on our website using a Publish to Web link. A few people have said they would like to do something similar for their organizations, so I’ll offer a couple of tips:

  1. Make sure the questions you ask aren’t violating any HR rules, and that employees know that some or all questions are optional. For example, I asked personal questions in the Fun Facts section about number of children employees have. This question was optional and our team felt comfortable answering it.
  2. Try to group your questions into categories so you can easily the separate sections and pages like I did. Then you can associate a color with each category.

If you do make something similar, I’d love to see it. Tweet me a link or screenshot at @mmarie.


Contact the Author | Contact DCAC

Power BI for Communication and Marketing

Published On: By:

We often focus on deep analysis and insights generated by machine learning when we talk about Power BI these days because it’s super cool and very fancy. But I think it’s important to remember that you can also use Power BI for simple communication of data. As humans, we are hard wired to process visual information faster than text alone. And it’s often more efficient and concise to convey information in a visual rather than text. We can show in one image what it takes paragraphs to describe. Outside of historical analysis and operational reporting, we can use data visualizations as an engaging way to convey simple facts in a communication or marketing effort. Many people do this with infographics.

Visualization can make simple numbers and facts more memorable and engaging. I enjoy using Power BI for this marketing communication purpose, so I made a report about the people I work with at Denny Cherry & Associates Consulting. I knew most of them before I started working at DCAC, so I was already aware that they were exceptional. But now you can see it, too, with our new Power BI report located at https://www.dcac.com/about-us/learn-more-about-us.

Screenshot of the home page of our About DCAC Power BI report

Data is some people’s preferred language. We can use it to discuss specific points as well as broader trends and comparisons. I have a sticker on my laptop that says “Please Talk Data To Me” for a reason. I can make sweeping statements about how my team is full of accomplished consultants, speakers, and authors. But it is much more impactful when you see the numbers: 27 Microsoft MVP awards and 8 VMWare VExpert awards over the years, a combined 113 years of experience with SQL Server, 86% of us having been a user group leader.

My Surface Pro tablet with a “Please Talk Data To Me” sticker on it

And because Power BI is interactive, you get to interact with my report, choosing which categories you want to learn more about. There is also a little guessing game built into the report. (Although there is currently a Power BI bug that I had to work around, so the answers are limited to values 1-800 and 2001-2300. But that is a blog post for another time, and for now you get a hint as to possible answers.) If I had just told you these stats, you might have listened, but you probably would have tuned out.

Our team even had fun browsing the report, seeing our collective achievements, and guessing who provided which answers.

Building the report

It was quick and easy to use Microsoft Forms to gather the data and then dump the responses to Excel. From there, I made a quick Power BI data model and put together my visualizations. The report is embedded on our website using a Publish to Web link. A few people have said they would like to do something similar for their organizations, so I’ll offer a couple of tips:

  1. Make sure the questions you ask aren’t violating any HR rules, and that employees know that some or all questions are optional. For example, I asked personal questions in the Fun Facts section about number of children employees have. This question was optional and our team felt comfortable answering it.
  2. Try to group your questions into categories so you can easily the separate sections and pages like I did. Then you can associate a color with each category.

If you do make something similar, I’d love to see it. Tweet me a link or screenshot at @mmarie.


Contact the Author | Contact DCAC
1 2 3 4 5 435

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner    Microsoft MVP    Microsoft Certified Master    VMWare Partner    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link