New SQL setup test

hi all

we will be moving our sql instance to a new san setup. and i have a week to test functionality.
i plan on using SQLIOSim to stress test the hardware setup,

When i have sql actually setup and a test application server pointing at it, i will then test various steps that were before taking a long time to complete/locking sql such as invoicing orders and despatching orders.

but can anyone advise how i could simulate 100+ users on the system? and have them actually query the db etc
malraffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gene_CypCommented:
A simple way:
Create some of the queries that interest you and insert them into a very simple Application.
Make the application autorun these queries, based on a timer.
Have several instances of this application running at the same time, preferably from more than one Computer.
0
malraffAuthor Commented:
Hi gene

thats sort of along the lines i was thinking, but what sort of application would allow me to do so?

i was sort of hoping some one had a script they created or use to simulate this
0
malraffAuthor Commented:
would profiler be my best option here?

eg if i take a profile from the live system, could i take the profile generated and replay it over and over on the test system while i was testing various applications?
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gene_CypCommented:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="DBConnection1" value="Initial Catalog=myDBName;Data Source=dbIPNumber;Persist Security Info=True;User ID=myusername;Password=mypwd" />
<add key="DBConnection2" value="Initial Catalog=myDBName;Data Source=dbIPNumber;Persist Security Info=True;Integrated Security=SSPI" />
</appSettings>
</configuration>

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Data.SqlClient;
using System.Configuration;

namespace Test_QueryDBSim
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void timerQuery_Tick(object sender, EventArgs e)
        {
            timer++; // 1-5
            TimeSpan before = new TimeSpan(DateTime.Now.Ticks);
            
            SqlConnection sqlConnection = new SqlConnection();
            if (CreateDBConnection(out sqlConnection, "DBConnection1"))
            {
                // Choose a query
                switch (timer)
                {
                    case 1:
                        DoQuery(sqlConnection, query1);
                        CountQuery();
                        break;
                    case 2:
                        DoQuery(sqlConnection, query2);
                        CountQuery();
                        break;
                    case 3:
                        DoQuery(sqlConnection, query3);
                        CountQuery();
                        break;
                    case 4:
                        DoQuery(sqlConnection, query4);
                        CountQuery();
                        break;
                    default: // 5th (and in general good to have default)
                        timer = 0; // reset - we have created 5 queries
                        DoQuery(sqlConnection, query5);
                        CountQuery();
                        break;
                }
            }
            CloseConnection(ref sqlConnection);
            TimeSpan after = new TimeSpan(DateTime.Now.Ticks);
            lastTime = after = before;
            lblTimeValue.Text = lastTime.Ticks.ToString();
        }

        private bool DoQuery(SqlConnection sqlConnection, string query)
        {
            bool success = false;
            SqlCommand sqlCommand = new SqlCommand();
            string dbCommandQuery = "";

            // Build Query
            dbCommandQuery = query;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = dbCommandQuery;

            // Fetch
            try
            {
                SqlDataReader sqlDR = null;
                sqlDR = sqlCommand.ExecuteReader();
                success = true;
            }
            catch (Exception ex)
            {
                success = false;
            }

            return success;
        }
        private void CountQuery()
        {
            countQueries++;
            lblQueriesValue.Text = countQueries.ToString();
            lblQueriesValue.Refresh();
        }

        int timer = 0;
        int countQueries = 0;
        TimeSpan lastTime = new TimeSpan(0, 0, 0);

// Make these 5 distinct queries
        string query1 = "SELECT mytable1.Field1, mytable2.Field2 FROM mytable1 INNER JOIN mytable2 WHERE mytable1.Field4 = 'blahblah' ";
        string query2 = "SELECT mytable1.Field1, mytable2.Field2 FROM mytable1 INNER JOIN mytable2 WHERE mytable1.Field4 = 'blahblah' ";
        string query3 = "SELECT mytable1.Field1, mytable2.Field2 FROM mytable1 INNER JOIN mytable2 WHERE mytable1.Field4 = 'blahblah' ";
        string query4 = "SELECT mytable1.Field1, mytable2.Field2 FROM mytable1 INNER JOIN mytable2 WHERE mytable1.Field4 = 'blahblah' ";
        string query5 = "SELECT mytable1.Field1, mytable2.Field2 FROM mytable1 INNER JOIN mytable2 WHERE mytable1.Field4 = 'blahblah' ";


         

        private bool CreateDBConnection(out SqlConnection sqlConnection, string database)
        {
            bool success = false;
            string connString = "";
            sqlConnection = new SqlConnection();
            try // to open connection
            {
                connString = ConfigurationManager.AppSettings[database];
                sqlConnection = new SqlConnection(connString);
                sqlConnection.Open();
                success = true;
            }
            catch (SqlException sqlEx)
            {
                success = false;
                MessageBox.Show("Exception Thrown: " + sqlEx.Message + "\n\nSource: " + sqlEx.Source + "\n\nStackTrace: " + sqlEx.StackTrace,
                                        MethodBase.GetCurrentMethod().Name);
            }
            return success;
        }


        private void CloseConnection(ref SqlConnection sqlConnection)
        {
            try
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();    // Close the connection
                    sqlConnection = null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception Thrown: " + ex.Message + "\n\nSource: " + ex.Source + "\n\nStackTrace: " + ex.StackTrace,
                                        MethodBase.GetCurrentMethod().Name);
            }
        }
    }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gene_CypCommented:
This is in C#, I went into the trouble of making it for you.
You need a basic winform with the following controls:
Timer and call it: timerQuery
Four labels and call them: lblQueries, lblQueriesValue, lblTime, lblTimeValue
 
The xml you see further above is an App.config file. I created two sample conenctions depending on whether you use username and password OR intergrated security
 
If something is unclear feel free to ask.
0
malraffAuthor Commented:
thanks very much gene for this effort!

ok, i will prob need some help here!

could you advise what to do with this? i can see where i add my tables etc, but how to start i have no idea :(
0
Gene_CypCommented:
What do you mean?
Do you have Visual Studio or Studio Express?
Create a C# winform and copy that code in. The XML is a config file which you can add by adding a new config file to your project via studio again and just copy and paste that xml.
0
malraffAuthor Commented:
apologies, iv never used studio before!, ill have a look now to see what to do !
0
Gene_CypCommented:
Ok, have a look at this:
http://www.softwareqatest.com/qatweb1.html#LOAD 
It's a link providing several possible resources for stress testing. (I have not personally used the applications provided in the link, there is an extensive list, which should help you find one that is free or almost free and reliable to use)
0
Gene_CypCommented:
(and don't apologise mate :) )
0
malraffAuthor Commented:
id really like to get ur script working :-) ! it could be very useful- and a pretty decent test

would you be able to give me a dummies guide on how to do the following:
"Create a C# winform and copy that code in. The XML is a config file which you can add by adding a new config file to your project via studio again and just copy and paste that xml."
0
Gene_CypCommented:
You have zero Visual Studio and programming knowledge so this is going to be a bit of a nightmare to explain.
 
Assuming you already have Visual Studio installed:
 
1) Run Visual Studio
2) File -> New -> Project...
3) Select Visual C# from the list of choices on the left
4) Select "Windows Forms Applications" from the templates on the right
5) Enter the following name: Test_QueryDBSim and press OK
6) Add the timer and four labels I listed above, be sure that they have those names (use their properties window on the right side)
7) Select the timer control once you've added it and in its  properties change:
Enabled: True
8) Click on the little "thunder" icon, which will take you to that control's events and you will see there: 'Timer' and a black to its right. DOUBLE CLICK on the blank. If you do it right it will create some code for you called:

private void timerQuery_Tick(object sender, EventArgs e) { }
 
 9) Top right corner of your studio , in the solution explorer sub-window, click on "References".
10) Right click on it and select "Add Reference..."
11) Select the .NET Tab and scroll down to where it says System.Configuration
12) Select it and press ok.
13) Expand your References in the Solution Explorer (top right) and CONFIRM with your eyes that there is now a line there called System.configuration
14) In that same Solution Explorer, scroll down and Select Form1
15) Right Click on it and select View Code. You should notice in your main window several lines of code
16) Delete the lines. Copy the code I've posted here and paste it in there. If you get any warnings about references and renaming press OK.
17) Goto your Solution Explorer (top right) and Select Test_QueryDBSim.
18) Right click on it -> Add -> New Item...
19) In the templates presented in front of you, select Application Configuration File
20) Ensure the name is app.config
21) Press the Add button
22) On your by now infamous Solution Explorer, right click on App.config and select Open
23) A window with XML will be shown. Delete the contents and Copy and Paste the XML I posted here
24)  Save the work.
25) Change the queries to something meaningful and the database connection string in the XML file
 26) Save the work and from the menu at the top: Debug -> Start Debugging
 
 
 
 
0
malraffAuthor Commented:
i think i see my prob, alot of studio components seem to be missing! ill try and get a full install and see how i get on!
0
Gene_CypCommented:
Studio Express is fine btw, if you don't have the full licenced product.
0
malraffAuthor Commented:
HI GENE

ok im getting there!

1 error...

Error      1      
The type or namespace name 'Form1' could not be found (are you missing a using directive or an assembly reference?)      C:\V B\Test_QueryDBSim\Test_QueryDBSim\Program.cs      18      33      Test_QueryDBSim

which relates back to
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());        



so i changed form 1 to Test_QueryDBSim;
which gave the error

Error      1      
'Test_QueryDBSim' is a 'namespace' but is used like a 'type'      C:\V B\Test_QueryDBSim\Test_QueryDBSim\Program.cs      18      33      Test_QueryDBSim





0
malraffAuthor Commented:
oh and one  warning

Warning      2      The variable 'ex' is declared but never used      C:\V B\Test_QueryDBSim\Test_QueryDBSim\Form1.cs      80      30      Test_QueryDBSim
0
Gene_CypCommented:
The actual Form in your Solution Explorer, should be called "Form1". The solution (project) itself should be called Test_QueryDBSim
 
 
0
malraffAuthor Commented:
yea thats the way i have it.. i think
vb.png
0
malraffAuthor Commented:
ok,in the code i right clicked on form1 > resolve> with Test_QueryDBSim

and the error has gone and the application has popped up!

now what :) is that it querying my DB - if iv configured correctly?

0
Gene_CypCommented:
Well, remember to put the right DB connection settings in the app config and to correctly select that string in the form1.cs code
Also remember to edit the queries.
Once that is done, attempt to run the app. When you compile it(build it) ensure it builds a release and a debug and you can find them in their respective folders on the Bin directory in your Project folder.
 
You can take the "release folder" in the bin, and that is the self-contained application. Starting the exe starts an instance of the program. You want 100 of them..  start 100 instances and watch things crash. ;)
I'd advice you to use several PCs btw if you have the luxury of that and split the instances between them.
0
malraffAuthor Commented:
on mine the .exe is the xml configuration? when i double click it opens the xml script!

i ammended  the code in the below ways, is that correct
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DBConnection1" value="Initial Catalog=GRNSQL001;Data Source=dbIPNumber;
Persist Security Info=True;User ID=sa;Password=G1rafFe5" />      -------------------here
    <add key="DBConnection2" value="Initial Catalog=GRNSLQ001;Data Source=dbIPNumber;Persist Security Info=True;Integrated Security=SSPI" />
  </appSettings>
</configuration>



and


 private void timerQuery_Tick(object sender, EventArgs e)
        {
            timer++; // 1-5 
            TimeSpan before = new TimeSpan(DateTime.Now.Ticks);

            SqlConnection sqlConnection = new SqlConnection();
            if (CreateDBConnection(out sqlConnection, "GRNSQL001")) ------- here

Open in new window

0
malraffAuthor Commented:
OH, AND I CHANGED QUERIES TO AS BELOW TO JUST GET ME STARTED
string query1 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='031324'";
        string query2 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100000'";
        string query3 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100001'";
        string query4 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100002'";
        string query5 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100003'";

Open in new window

0
malraffAuthor Commented:
ahh i thought i was nearly there!

i appreciate the time you have put in for me gene, ill post the code im using and if you get 2 mins to have a quick run over it thats great, if not never worry! its not the easiest thing to pass over the web!


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Data.SqlClient;
using System.Configuration;

namespace Test_QueryDBSim
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void timerQuery_Tick(object sender, EventArgs e)
        {
            timer++; // 1-5 
            TimeSpan before = new TimeSpan(DateTime.Now.Ticks);

            SqlConnection sqlConnection = new SqlConnection();
            if (CreateDBConnection(out sqlConnection, "DBConnection1"))
            {
                // Choose a query 
                switch (timer)
                {
                    case 1:
                        DoQuery(sqlConnection, query1);
                        CountQuery();
                        break;
                    case 2:
                        DoQuery(sqlConnection, query2);
                        CountQuery();
                        break;
                    case 3:
                        DoQuery(sqlConnection, query3);
                        CountQuery();
                        break;
                    case 4:
                        DoQuery(sqlConnection, query4);
                        CountQuery();
                        break;
                    default: // 5th (and in general good to have default) 
                        timer = 0; // reset - we have created 5 queries 
                        DoQuery(sqlConnection, query5);
                        CountQuery();
                        break;
                }
            }
            CloseConnection(ref sqlConnection);
            TimeSpan after = new TimeSpan(DateTime.Now.Ticks);
            lastTime = after = before;
            lblTimeValue.Text = lastTime.Ticks.ToString();
        }

        private bool DoQuery(SqlConnection sqlConnection, string query)
        {
            bool success = false;
            SqlCommand sqlCommand = new SqlCommand();
            string dbCommandQuery = "";

            // Build Query 
            dbCommandQuery = query;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = dbCommandQuery;

            // Fetch 
            try
            {
                SqlDataReader sqlDR = null;
                sqlDR = sqlCommand.ExecuteReader();
                success = true;
            }
            catch (Exception ex)
            {
                success = false;
            }

            return success;
        }
        private void CountQuery()
        {
            countQueries++;
            lblQueriesValue.Text = countQueries.ToString();
            lblQueriesValue.Refresh();
        }

        int timer = 0;
        int countQueries = 0;
        TimeSpan lastTime = new TimeSpan(0, 0, 0);

        // Make these 5 distinct queries 
        string query1 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='031324'";
        string query2 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100000'";
        string query3 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100001'";
        string query4 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100002'";
        string query5 = "SELECT InvMaster.Description FROM SysproCompanyT.InvMaster where StockCode ='100003'";




        private bool CreateDBConnection(out SqlConnection sqlConnection, string database)
        {
            bool success = false;

string connString = "Initial Catalog=GRNSQL001;Data Source=dbIPNumber;Persist Security Info=True;User ID=sa;Password=G1rafFe5";                      -------- i added this ---------------

            sqlConnection = new SqlConnection();
            try // to open connection 
            {
                connString = ConfigurationManager.AppSettings[database];
                sqlConnection = new SqlConnection(connString);
                sqlConnection.Open();
                success = true;
            }
            catch (SqlException sqlEx)
            {
                success = false;
                MessageBox.Show("Exception Thrown: " + sqlEx.Message + "\n\nSource: " + sqlEx.Source + "\n\nStackTrace: " + sqlEx.StackTrace,
                                        MethodBase.GetCurrentMethod().Name);
            }
            return success;
        }


        private void CloseConnection(ref SqlConnection sqlConnection)
        {
            try
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();    // Close the connection 
                    sqlConnection = null;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception Thrown: " + ex.Message + "\n\nSource: " + ex.Source + "\n\nStackTrace: " + ex.StackTrace,
                                        MethodBase.GetCurrentMethod().Name);
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}

Open in new window

0
malraffAuthor Commented:
and xml
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="DBConnection1" value="Initial Catalog=GRNSQL001;Data Source=dbIPNumber;Persist Security Info=True;User ID=sa;Password=G1rafFe5" />
    <add key="DBConnection2" value="Initial Catalog=GRNSLQ001;Data Source=dbIPNumber;Persist Security Info=True;Integrated Security=SSPI" />
  </appSettings>
</configuration>

Open in new window

0
Gene_CypCommented:
Malraff don't post the actual details here for the queries and DB access!!!! (bad practise man) ;) Never publicise those!
Anyhow, in terms of the files themselves, you probably don't have your windows settings to show the extensions of your files. Thus the three files there, are named:
Test_QueryDBSim
Test_QueryDBSim .exe
Test_QueryDBSim
 
View by Details, and run the file that says:
Test_QueryDBSim (and is of application type) . Its true name is Test_QueryDBSim.exe but by default your windows is not showing the file extensions. The other file that reads Test_QueryDBSim.exe is actually Test_QueryDBSim.exe.config and notice in its type it's of XML Configuration type.
0
Gene_CypCommented:
The queries is ok to share but the DB, no! especially username and pwd! ;)
0
malraffAuthor Commented:
oops! yea realised my mistake as i posted, usually not so careless!!

is there any way for studio to show what the app is trying to do? and do you know is there any limits on connecting to networked sql with express? as when i rty to setup a data source it does not find any sql servers
0
Gene_CypCommented:
Not sure I understood what you are trying to ask.
 
The app itself is self contained and has nothing to do with SQL express. You shouldn't run the app through studio itself, that's why I directed you at the bin. Just get the release build and run it. If your queries and program works, it should jsut keep counting how many queries it has executed.
0
malraffAuthor Commented:
where would i see the count? all i have on the form is 4 labels 1-4 but no place for anything to be shown?
0
malraffAuthor Commented:
i meant when in debug, can i see if it is successfully querying sql etc?
0
Gene_CypCommented:
If no exception is thrown then it was a success.
I haven't added any code that actually "examines" the data it receives because that's not part of the program's job.
If you want to see it in action though:
Add a BREAK POINT inside:

private void timerQuery_Tick(object sender, EventArgs e)
0
Gene_CypCommented:
Using F10 and F11(to go inside a method) you can track the operations there.
You press 'Paly' to move on, but disable the breakpoint before doing so because it'll reach that point again.
0
malraffAuthor Commented:


the program opens up and no errors are returned, but when i check sql i see no activity on the db i am querying - im right in thinking that this app will continuously query?

the labels on the form, should i be able to see there values?  as all i have is 4 labels with no where for data to be illustrated as below?

i think ill give up :(  would be worth my while getting some basic programming experience!
Capture.PNG
0
Gene_CypCommented:
Did you remember to name the labels as instructed further above? And did you then copy and paste the code I gave as is?
Did you also remember to ensure that the TIMER control that you added is ENABLED by changing its ENABLE property to true?
(You can do these things by right clicking on "Form1.cs" and selecting View Designer, then click on that control and go to its properties sub-window which should be located at the bottom right corner of the studio)
At the top of that property window you will see (Name).
0
Gene_CypCommented:
Malraff I feel as if I've put much more effort than you in this. At least take the time to get it to work. The instructions are very simple, especially that detailed list I've posted much further above.
I've also provided you a link of resources for applications that stress test for you.
You need to do some work, in the applicaiton I gave, you just have to follow detailed instructions. In the link I gave, you have to do the exploring bit yourself. Two different approaches that provide a solution to your problem.
0
malraffAuthor Commented:
gene i really appreciate the effort you put in for me and is without a doubt greater than my effort

but i have followed ur steps to the letter! its simply my lack of knowldge in the area thats hindering me!
and ill be leaving you the points regardless!
hopefully this eve ill get some free time to sit down and give it another lash!
0
Gene_CypCommented:
If it was just for the points I'd have stopped ages ago. ;)
I suggest you try the following mate:
1) Rename the root folder of the project you did
2) Start implementing the instructions I gave from scratch and if/when you encounter something you are unsure of, write it down on your notepad.
3) Post your notepad notes here
Also, DO go through that link I gave you in a separate reply.
0
Gene_CypCommented:
Malraff did you get your project to work?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.