· 8 years ago · Apr 03, 2018, 08:40 AM
1/*
2Description: A record of what what VMs we want located where.
3Create sample lookup/static tables that defines a grouping of what App and SQL VMs
4Should be kept on the same ESXHost and/or the same site.
5It would be unusual to seperate related servers across sites but possible.
6Note that we are specifying which ESXHost the VM should be on but
7we are specifying which cluster and which primary site.
8*/
9
10USE Test
11go
12if exists (select 1 from sysobjects where name = 'ESXHost' and type = 'U') DROP TABLE ESXHost
13GO
14
15CREATE TABLE ESXHost
16(
17ESXHostID SMALLINT IDENTITY (1,1),
18ESXHostName VARCHAR(55),
19Cluster VARCHAR(16),
20Site TINYINT,
21PerformanceProfile CHAR(1),
22HostRoleID TINYINT
23)
24GO
25
26INSERT INTO ESXHost VALUES ('H1','C1',1,'H',1)
27INSERT INTO ESXHost VALUES ('H2','C1',2,'M',1)
28INSERT INTO ESXHost VALUES ('H3','C1',1,'L',2)
29INSERT INTO ESXHost VALUES ('H4','C1',2,'H',2)
30INSERT INTO ESXHost VALUES ('H5','C1',1,'L',2)
31INSERT INTO ESXHost VALUES ('H6','C1',2,'L',2)
32INSERT INTO ESXHost VALUES ('H7','C1',1,'L',2)
33INSERT INTO ESXHost VALUES ('H8','C1',2,'L',2)
34INSERT INTO ESXHost VALUES ('H9','C1',1,'L',2)
35INSERT INTO ESXHost VALUES ('H10','C1',2,'L',2)
36INSERT INTO ESXHost VALUES ('H11','C2',1,'L',2)
37INSERT INTO ESXHost VALUES ('H12','C2',2,'L',2)
38
39GO
40
41
42
43if exists (select 1 from sysobjects where name = 'host_vm_grouping' and type = 'U') DROP TABLE host_vm_grouping
44GO
45CREATE TABLE host_vm_grouping
46(
47ID int IDENTITY (1,1),
48Cluster varchar(16), -- what cluster this VM is related to via the ESXHost
49--PrimarySite tinyint, -- which site the VM be in by default
50PreferedHostID SMALLINT,
51VM varchar(55), -- the name of the Virtual Machine
52VM_Type varchar(55), -- the role, eg. a SQL Server or Application server
53AppGroup varchar(55), -- the name of an Application or Service related to one or more VMs
54--SameESXHost binary, -- a flag to indicate if the VM should be on the same ESXHost or not
55--SameSite binary, -- should this VM should be in the same site as the other related VMs
56PerformanceRequirement char(1) -- H, M or L
57)
58GO
59
60SELECT * FROM ESXHost
61
62-- App1: VMs to be grouped together on the same host, in site 1 by default
63INSERT INTO host_vm_grouping VALUES('C1',1,'VM1','APP','App1','M')
64INSERT INTO host_vm_grouping VALUES('C1',1,'VM2','APP','App1','M')
65INSERT INTO host_vm_grouping VALUES('C1',1,'VM3','SQL','App1','M')
66
67-- App2: VMs don't have to be on the same host but it's ok if they are. Default site 2
68INSERT INTO host_vm_grouping VALUES('C2',2,'VM4','APP','App2','L')
69INSERT INTO host_vm_grouping VALUES('C2',2,'VM5','SQL','App2','L')
70
71-- App3: a mixed role VM both APP and SQL. Any host is ok in site 1.
72-- the values for 'SameHost' and 'SameSite' could be NULL as only one VM for this App group.
73INSERT INTO host_vm_grouping VALUES('C1',2,'VM7','Mixed','App3','M')
74
75-- App4: VMs to be grouped together on the same host with in site 1 by default
76-- in this example, two of the app server VMs can be on any host
77INSERT INTO host_vm_grouping VALUES('C1',3,'VM8','APP','App4','M')
78INSERT INTO host_vm_grouping VALUES('C1',3,'VM9','APP','App4','M')
79INSERT INTO host_vm_grouping VALUES('C1',3,'VM10','APP','App4','H')
80INSERT INTO host_vm_grouping VALUES('C1',3,'VM11','APP','App4','H')
81INSERT INTO host_vm_grouping VALUES('C1',3,'VM12','SQL','App4','H')
82go
83
84if exists (select 1 from sysobjects where name = 'HostRole' and type = 'U') DROP TABLE HostRole
85GO
86
87CREATE TABLE HostRole
88(
89ID TINYINT IDENTITY(1,1),
90HostRole VARCHAR(16) -- Mixed, SQL, App, Infra
91)
92GO
93
94INSERT INTO HostRole VALUES ('SQL')
95INSERT INTO HostRole VALUES ('App')
96INSERT INTO HostRole VALUES ('Mixed')
97INSERT INTO HostRole VALUES ('Infra')
98
99GO
100
101
102
103if exists (select 1 from sysobjects where name = 'Get_VMInfo' and type = 'U') DROP TABLE Get_VMInfo
104GO
105-- simulated data for current VM host location to allow queries to be developed
106CREATE TABLE Get_VMInfo
107(
108Cluster VARCHAR(16),
109ESXHostName VARCHAR(55),
110Site TINYINT, -- Would be derived from the VM name S1vmname = 'Site 1' S2vname = 'Site 2'
111VM VARCHAR(55)
112)
113GO
114-- Alter the data inserts to generate different test data sets
115
116-- in this case, VM3 should be on the same host as VM1 and VM2 but it isn't
117INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM1')
118INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM2')
119INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM3')
120
121-- in this case, the two VMs are where they should be
122INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM4')
123INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM5')
124
125-- In this case, just one VM to be concerned with and the only constraint is that
126-- the VM needs to have a primary site = 1 and Performanc Profile 'M' (host_vm_grouping)
127INSERT INTO Get_VMInfo VALUES('C1','H3',1,'VM7')
128
129
130-- in this case, we have a larger number of VMs with different rules to test
131-- whereby 3 of the 5 servers need to located on the same host but this doesn't apply
132-- to the other two VMs
133INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM8')
134INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM9')
135INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM10')
136INSERT INTO Get_VMInfo VALUES('C1','H1',1,'VM11')
137INSERT INTO Get_VMInfo VALUES('C1','H2',1,'VM12')
138
139
140/* */
141
142
143USE test
144GO
145-- the host_vm_grouping table represents the rules for how VMs are located on Hosts
146-- given App groups and performance requirements, etc.
147select * from host_vm_grouping
148
149-- The ESXHost table represents static/lookup data related to config/performance profile
150-- of the ESX hosts. This table would be maintained manually.
151-- The purpose of this table to provide the base of info in terms of what ESX hoste exists,
152-- the architecture in terms of what Cluster and Site they relate to
153-- and the Peformance category - eg. the Host many have more processors, memory, etc
154-- We might want to look at how to use this to ensure only certain types of VMs go on
155-- specific hosts - eg. only SQL Server VMs on this host, etc
156
157select * from ESXHost
158
159SELECT
160 h.Cluster,
161 h.ESXHostName,
162 hr.HostRole,
163 h.PerformanceProfile,
164 h.Site
165FROM
166 ESXHost h
167 LEFT JOIN HostRole hr
168 ON h.ESXHostID = hr.ID
169
170
171-- Starting with the simplest query - are the VMS on the correct Cluster
172-- Note, it would be very rare that a VM was on the wrong cluster but a good case to start
173select
174 g.AppGroup,
175 g.VM,
176 g.VM_Type,
177 i.ESXHostName as 'actualESXHostName',
178 h.ESXHostName as 'targetESXHostName',
179 i.VM as 'actualVM',
180 g.PerformanceRequirement,
181 --g.PrimarySite,
182 --g.SameESXHost,
183 --g.SameSite,
184 i.*,
185 h.*
186from
187 host_vm_grouping g -- grouping reqired but esxhost name not included
188 INNER JOIN Get_VMInfo i -- snapshot of current allocation of VMs ot Hosts
189 ON g.VM = i.VM
190 INNER JOIN ESXHost h -- static host info
191 ON i.ESXHostName = h.ESXHostName
192 LEFT JOIN HostRole hr
193 ON h.ESXHostID = hr.ID
194where
195 g.AppGroup = 'App4'
196
197
198
199select * from host_vm_grouping
200select * from Get_VMInfo
201select * from ESXHost
202
203
204create table #test
205(
206vm varchar(4),
207AppGroup varchar(8),
208SameESXHost tinyint,
209ActualHost char(2)
210)
211GO
212
213;with cte_VMMon
214AS
215(
216select
217 g.Cluster as 'DesiredCluster',
218 i.Cluster as 'ActualCluster',
219 CASE
220 WHEN g.Cluster <> i.Cluster THEN 'Wrong'
221 ELSE 'Right'
222 END as 'ClusterCheck',
223 --g.PrimarySite as 'DesiredSite',
224 --i.Site as 'ActualSite',
225 --CASE
226 -- WHEN g.SameSite = 1 AND g.PrimarySite <> i.Site THEN 'Wrong'
227 -- ELSE 'Right'
228 --END AS 'SiteCheck',
229 g.vm,
230 g.VM_Type,
231 g.AppGroup,
232 --g.SameESXHost,
233 i.ESXHostName as 'ActualHost',
234 --g.SameSite,
235 row_number() over (partition by i.ESXHostName order by g.vm ) as siteRank2,
236 g.PerformanceRequirement
237from
238 host_vm_grouping g
239 INNER JOIN Get_VMInfo i
240 ON g.vm = i.vm
241where
242 g.AppGroup = 'App4'
243)
244select * from cte_VMMon
245
246
247select
248 'insert into #test values(' + quotename(vm,'''') + ',' + quotename(AppGroup,'''') + ',' + cast(cast(SameESXHost as int) as char(1)) + ',' + quotename(ActualHost,'''') + ')'
249 --vm,
250 --AppGroup,
251 --cast(SameESXHost as int) as SameESXHost,
252 --ActualHost
253 --SameSite
254from
255 cte_VMMon
256
257--SELECT
258-- max(Actualhost) as Actualhost,
259-- max(siteRank2) as count
260--FROM
261-- cte_VMMon
262--group BY
263 ActualHost
264
265
266insert into #test values('VM8','App4',0,'H1')
267insert into #test values('VM9','App4',0,'H1')
268insert into #test values('VM10','App4',1,'H1')
269insert into #test values('VM11','App4',1,'H1')
270insert into #test values('VM12','App4',1,'H2')
271
272select * from #test